Friday, November 02, 2012

Moving Sql data files to different location


It is always good to have database files to be placed on a non-system partition (system partition is where default program files and windows folder resides)

Last week, unfortunately my PC got corrupted, had to restore to 3 days old system image of windows, and I lost my sql data. Now for future, I have taken a preventive measure, and moved my sql database files to non-system drive.

Here are the steps,

Database name is testDB,

Note: make sure new location is not read only, has sufficient privileges to Administrator account, and the drive is uncompressed 

Step 1 : Create a new location 

New location is D:\MSSQL\DATA\

Step 2 : Run these commands in master database

ALTER DATABASE testDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE testDB SET OFFLINE;

ALTER DATABASE testDB MODIFY FILE
(
   Name = testDB,
   Filename = 'D:\MSSQL\DATA\testDB.MDF'
);

ALTER DATABASE my MODIFY FILE
(
   Name = testDB_log,
   Filename ='D:\MSSQL\DATA\testDB_log.LDF'
);

Step 3 : Move original sql data files from C:\program files\mssql\data to newly created location

Make sure the newly pasted files are not read-only, remove the read-only or compression settings.


Step 4: Move original sql data files from C:\program files\mssql\data to newly created location

Bring the database to online mode

ALTER DATABASE testDB SET ONLINE;
ALTER DATABASE testDB SET MULTI_USER;

Wednesday, June 20, 2012

Another way of pagination in sql server


Today, I wrote a paginating query using ranking function row_number(), with help of my team mate Neha. Initial idea was from her, and later I hijacked it to post it here. :). Poor Neha..
Ceiling function seems to be crux of it.  First 3 columns are data columns, rest are informational for paging data.


select
          *
from
(
          SELECT                                                                         
          ID                                                      --Data Columns
          ,GrandSlamLocation                          --Data Columns    
          ,GrandSlamLogin                               --Data Columns
          ,ROW_NUMBER() OVER(ORDER BY ID) [S.No]                                             
          , ceiling(ROW_NUMBER() OVER(ORDER BY ID)/ 10.00)  as Page
          ,count(*) over() as TotalRecords
          ,ceiling(count(*) over()/10.00) as TotalPages
          from
          (
                   --Base Query Start
                   select
                             ID,                                  --Data Columns
                             GrandSlamLocation,      --Data Columns    
                             GrandSlamLogin            --Data Columns
                   from  
                             Ticket
                   --Base Query End
          )A
           
)B where Page =  1

Tuesday, June 05, 2012

Tally table - equivalent of "select * from dual" in oracle

This sql query genrates unlimited sequential rows/virtual rows, using CTE


; WITH

Table1 AS (SELECT 1 N UNION ALL SELECT 1 N),    -- 2            ,              2 raise to power 1
Table2 AS (SELECT 1 N FROM Table1 x, Table1 y),            -- 8            ,   2 raise to power 3
Table3 AS (SELECT 1 N FROM Table2 x, Table2 y),            -- 128         ,   2 raise to power 7
Table4 AS (SELECT 1 N FROM Table3 x, Table3 y),            -- 32768        , 2 raise to power 15
Table5 AS (SELECT 1 N FROM Table4 x, Table4 y),           -- 2147483648 , 2 raise to power 31
tally as
(
                   SELECT
                             ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N                      
                   FROM
                             Table1 , Table2 , Table3, Table4 --, Table5
                             -- you can delete or add tables here to increase decrease the rows, or
)
select * from tally where n < 5000
-- you can control number of rows here also

Truncate your database log file

CREATE PROC [dbo].[PROC_TruncateLog]
AS BEGIN
      DECLARE @DBName sysname,
                  @logName sysname
      --
      SET @DBName = DB_NAME()
      --
      BACKUP LOG @DBName WITH NO_LOG
      --
      DECLARE logCursor CURSOR FOR
      SELECT name
      FROM sysfiles
      WHERE groupid = 0
      --
      OPEN logCursor
      FETCH NEXT FROM logCursor
      INTO @logName
      WHILE @@FETCH_STATUS = 0 BEGIN
            SELECT @logName = RTRIM(@logName)
            DBCC SHRINKFILE (@logName)

            FETCH NEXT FROM logCursor
            INTO @logName
      END
      CLOSE logCursor
      DEALLOCATE logCursor
END