Archive for the ‘T-sql’ Category

Query Complete SQL error log in one GO !!

July 14, 2014 Leave a comment

Hi All,

this post coming after a long time.


if you need to search a particular word or error code in the SQL error long,then you would have to do it one by one .

i.e .. you will proceed like below (let say for example we are looking for  connection timeout errors in Always ON setup, this could be any error or a

particular  word you are interested in looking for)

to query the current SQL error log

EXECmaster.dbo.Xp_readerrorlog 0,1,N‘A connection timeout’

0 –> current logs  | 1 –> SQL error log (also if you specify nothing in this place .. it is taken as SQL error log by default)

third parameter is something you are searching for in the error log ..

for the previous error log (ie errorlog.1) we have to use EXECmaster.dbo.Xp_readerrorlog 1,1,N‘A connection timeout’

so this becomes tedious over time .. let put this in a loop as below  …

assuming you have total of 30 previous long retained ..

use tempdb


CREATE TABLE #read_error_log


logdate DATETIME,

processinfo VARCHAR(200),

errorlogtext VARCHAR(max)


declare @int int= 0

while (@int <= 30)


INSERT INTO #read_error_log

EXEC master.dbo.Xp_readerrorlog @int,1,N’A connection timeout’

set @int= @int+ 1



FROM  #read_error_log


–TRUNCATE TABLE #read_error_log

–DROP TABLE #read_error_log



The above query will give you results much faster then query individual error logs ..

Refer below Links for more details on reading error logs ::

Enjoy 🙂



Running DBCC CHECKDB on all databases at once !!!

November 26, 2013 2 comments

Writing with Ink

Issue ::

some times it so happens .. that the  storage assigned to SQL servers may take a sudden

unexpected reboot .. and in those moments we may have LOST WRITES in

          databases .. which can cause them to be marked as suspect or causing 

          issues while accessing objects … 


in those moments we have to ensure consistancy and allocation across all DBs

are intact .. running DBCC CheckDB manually on each and every option is time

consuming (plus multiple windows  are to be opened)

SCRIPT ::  below script will help us run DBCC checkDB on all DBs once we have count of all DBs present on our instance ..  (the other option you have to achieve the same is to use CHECKDB job .. but with below script you will be able to view the errors in Query analyser itself)


1> use sp_helpdb .. to check total number of databases present on your instance …

according to the total number of DBs .. use dbid >= * and dbid <= * to cover all DBs

—Script to Run in different windows

—— by commenting and dbid >= * and dbid <= * part in the where clause



—————————————-SCRIPT START ————————————————-

DECLARE @name VARCHAR(50) — database name



FROM master.dbo.sysdatabases
WHERE name NOT IN (‘tempdb’)
and dbid >= 1 and dbid <= 20 —- window 1
–and dbid >= 21 and dbid <= 50 —- uncomment and run from QA window 2
–and dbid >= 51 and dbid <= 100 —- uncomment this and run from QA window 3
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name



–SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’



set @SQL = ‘DBCC CHECKDB’ + ‘ ‘ + ‘(‘+@name + ‘)’
print @SQL

exec (@SQL)



FETCH NEXT FROM db_cursor INTO @name



CLOSE db_cursor
DEALLOCATE db_cursor



—————————————-SCRIPT END————————————————-




you should see the output similar to above ..

go throught the errors and see if Allocation or integrity errors are reported for any DB …

accordingly use Repair_REBUILD or REPAIR_ALLOW_DATA_LOSS of DBCC CheckDB ..

alway prefer to use lastest Good backup over using above repair option !!!!




Delete OLD SQL Backups using Powershell in one go !!!

November 23, 2013 Leave a comment

issue :

some times the maintance plans do not delete old Backups files (based on their age) ,that we have intended it to do

also the delete maintance task (subtask) may also skip files .. based on their creation date or AGE ..

manually deleting the old .BAK or any other files is not really inspiring after a certain time ..

Solution : 

we can use Powershell (PS) to help us acheive the same result .. with few lines ..

SETUP :: im backing up the databases into a test folder .. but the PS script can used to do the same tasks for any storage filer server .. that you must be using to store backups — JUST MAKE SURE YOU PASS the CORRECT PATH ..

use below script to backup all database to a sepcfic path

USE master


DECLARE @int varchar(15)

declare @Rand varchar(15)

DECLARE @SQL varchar(200)

declare @path varchar(200)

DECLARE @Cur varchar(20)

declare curr CURSOR FOR

SELECT name from sys.databases where database_id <> 2

OPEN curr


FROM curr INTO @cur



–declare @var char(20)

PRINT @cur
–set @var = @cur

set @int = convert(varchar(20),getdate(),112)

set @rand = substring(convert(varchar(10),rand()*1000,112),0,4)

set @path = ‘D:\POWERSHELL\TEST\New Folder\’+@cur+’_’+@int+’_’+@rand+’.BAK’

–print @path

backup database @cur to disk = @path

FROM curr INTO @cur


CLOSE curr



Above script is send all full backups to path –> D:\POWERSHELL\TEST\New Folder\    (choose any path .. that works for you !!!)

once we have backups in place ..  (or we know which Backups are to deleted according to their age ) .. start you powershell ..

START –> RUN –> POWERSHELL.EXE    (will work on all machines above windows XP,Server 2003 ) .. for this 2 .. you need to download and install

you do not have to change you path .. to the folder .. from where you want to delete files .. (example in case of external storage location which is accessed like \\DATADOMAINSERVER01\SQLBACKUP\DAILY_Backups\) .. you can run the powershell script ..  from wherever your current path ..

in my case im switching to the location which holds the path .. because i need to show few options ..

the -Recurse option present in DIR or get-CHILDITEM is able to traverse through all sub folders under the parent folder ..

hence with this option you will be able to delete old files in a external storage path like —


below results show .. that DIR – Recurse is able to trverse through D:\POWERSHELL\TEST\ and a subfolder named New folder

(we excluded the BAK files .. so as to minimize the outcome)


below command will give you backups created 3 hours back .. you will be looking for much older backups and use below command

which works with days instead of hours  (i have used below .. as a DEMO )

 USE addhours(-30)   (to get a count of files and folders which creation date older then 30 days )

get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse | Where-Object {$_.creationdate-lt ($(getdate).adddays(-30))} | measure


NOW :  we only want to work with .BAK files and possibly may like to exlcude some files from deletion command … so we will use -include *.BAK (or *.SAFE) and -Exlcude  (to exclude any type of files example *.PS1 )

if you use SQL SAFe for backups or any other Backup utility use the required extention

use below command

basically .. we are using -lt (less then comparision) to filter out files older then 30 days .. (in example its older then 2 hours)

below command will give .. only 2 files .. of type *.BAK ..

 get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse -include *.BAK -exclu
de *.PS1 | Where-Object {$_.creationtime -lt ($(get-date).adddays(-30))} | select -First 2


use below command to get a exact count of *.BAK files .. you will be deleting

 get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse -include *.BAK

-exclude *.PS1 | Where-Object {$_.creationtime -lt ($(get-date).adddays(-30))} | measure 


if you trying to get a count of files older then 30 days .. on large file server .. above command will take some time to complete ..

———-command to delete files older then certains Days ————————

********** take care of below points **************

1> mention correct path from where you want to delete backup files (even sub-folder will be taken into account)

2> mention -include *.BAK .. to make sure only backup files are deleted .. also if needed exclude any application or config file

if its possibly on the mentioned path ..

3> make sure to mention correct AGE or no. of days you want to delete files  adddays(-30)) .. means you want to delete a month old files or 2 months old files ETC 


 get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse -include *.BAK -exclude *.PS1 | Where-Object {$_.creationtime -lt ($(get-date).adddays(-30))} | remove-item -force




Above command will delete the backup files of type (*.BAK) and exclude the files of type (*.PS1)  older then 30 days

under path D:\POWERSHELL\TEST\New Folder and any sub folder .. it may have .. 

try out this command .. and let me know your thoughts ..



Msg 3140, Level 16, State 5, Line 1 Could not adjust the space allocation for file ‘DBfilename’

October 1, 2013 4 comments

We had database which had few tables with image/text (LOB) datatypes .. and after some archeiving of older data … we wanted to reclaim the free space in the database ..

normal Shrink command was not able to reclaim any of free space in the database .. so i decided to go with notruncate and truncate_only options .. but the second part was failing with an error .. Could not adjust the space allocation for file



print ‘start time’
print getdate()
DBCC shrinkfile (1,notruncate)
print ‘completed first step’
print getdate()
DBCC shrinkfile (1,truncateonly)
print ‘completed 2nd step’
print getdate()

out put :: 

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
completed first step
Oct 1 2013 2:17AM
Msg 3140, Level 16, State 5, Line 1
Could not adjust the space allocation for file ‘DBfilename’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
completed 2nd step
Oct 1 2013 2:17AM

Solution :

after aboveerror i tried to shrink the DB with DBCC Shrinkfile (1,targetSize)  (which had initially failed to shrink any free space)

but this time i was able to shrink the DB to required size …  so solution to error such as this is

just Shrink the DB with t-sql script  DBCC Shrinkfile (1,targetSize)  or use GUI to shrink the DB … 

it should complete real fast as all the shrinking has been already done by DBCC shrinkfile (1,truncateonly) …

ue below script to confirm the reduction is the overall DB files ..

convert(decimal(12,2),round((a.size-fileproperty(,’SpaceUsed’))/128.000,2)) ,
NAME = left(a.NAME,15),
dbo.sysfiles a

refer below link for internals of shrink operation


What’s my OS bit LEVEL 32 or 64

June 30, 2013 3 comments

recently I needed to check OS level of few servers   most servers in my environment , I was looking for query I could run from

CMS .. to get a consolidated CentralManagementServerdata from all servers .

I got an initial idea from link .. A big Thanks to Thomas LaRock

I had few servers where DMV or 2005 + onwards features or tables where

not available .. so I have modified Thomas’s script a bit to suit my needs

below script can be RAN on CMS or individual SQL server

output will have Servername, OS bit level,SQL bit level and whether Server

is VM (works for Hypervisor or VM) or Physical


@@servername ‘Servername’,


when RIGHT(SUBSTRING(@@VERSION,CHARINDEX(‘<‘,@@VERSION), 4), 2)like ’64’ then ’64’

else ’32’



when RIGHT(SUBSTRING(CONVERT(varchar,SERVERPROPERTY(‘Edition’)),CHARINDEX(‘(‘,CONVERT(varchar,SERVERPROPERTY(‘Edition’))), 3), 2)like’64’ then’64’

else ’32’

end ‘SQL TYPE’,

case when substring(@@version,CHARINDEX(‘Hypervisor’,@@version),10)like’Hypervisor’ then ‘VM’

when substring(@@version,CHARINDEX(‘VM’,@@version),2)like’VM’ then ‘VM’
else ‘physical’ END ‘SERVER TYPE’


let me know if you are facing any issue with this script ..

enjoy 🙂

set SQL error log retention period through T-SQL

April 18, 2013 Leave a comment

we all may need to tweak the SQL error log retention period on a single instance or list of SQL server VIA CMS (central management server)

below 2 commands are quite useful for achieving the same


READ the current setting of sql error log retention 

/* USE [master] GO EXEC xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’ GO */

you may receive below error on few server .. you may want to  go ahead with the update command and come back to see the results of xp_instance_regread

possible error message for some server ..

RegQueryValueEx() returned error 2, ‘The system cannot find the file specified.’ servername(username): Msg 22001, Level 1, State 1

set  the LOG retention sql error to 20 days 

/* USE [master] GO EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’, REG_DWORD, 20 GO */

above command will set the retention to 20 days and you may rerun EXEC xp_instance_regread to confirm the same setting

how to add yourself to sysadmin role

April 9, 2013 1 comment

one of the best thing about attending SQL Saturday’s is the people you meet and tricks you learn .. (hmmm ok .. even the food included)

i come across similar webcast were i learned below 2 methods to add yourself  as sysadmin on any SQL instance ..

example : if the SQL installation was done by someone else and OS admins were never given sysadmins priviliges on SQL instance

so after you get yourself added to OS admin .. you can follow any of the below methods to achieve the same …

NOTE: both methods cause SQL restart .. so take a note of the same …

SQL Saturday 119 Lunch 017

SQL Saturday 119 Lunch 017 (Photo credit: Michael Kappel)

International Bibliography of Periodical Liter...

International Bibliography of Periodical Literature (Photo credit: Wikipedia)

Option 1: (the logged in user gets added in sysadmin group)

download the cmd file from above link and save it onto the server .. where you want to add yourself to sysadmin ..

double click the cmd file and it will ask for instance name .. default is taken as the SQL express edition instance …

only enter the instance name (example for instance node-1\SQL2012_INST …. we will just enter SQL2012_INST .. not the network name)

below messages will appear in the cmd window .. (Note : it will cause SQL to restart as it puts instance in single-user mode and restarts it twice )

Adding ‘NODE-1\test’ to the ‘sysadmin’ role on SQL Server instance ‘SQL2012_INST
Verify the ‘MSSQL$SQL2012_INST1’ service exists …
Stop the ‘MSSQL$SQL2012_INST1’ service …
The SQL Server (SQL2012_INST1) service is stopping.
The SQL Server (SQL2012_INST1) service was stopped successfully.

Start the ‘MSSQL$SQL2012_INST1’ service in maintenance mode …
Add ‘NODE-1\test’ to the ‘sysadmin’ role …
Stop the ‘MSSQL$SQL2012_INST1’ service …
The SQL Server (SQL2012_INST1) service is stopping…
The SQL Server (SQL2012_INST1) service was stopped successfully.

The SQL Server (SQL2012_INST1) service is starting.
The SQL Server (SQL2012_INST1) service was started successfully.

‘NODE-1\test’ was successfully added to the ‘sysadmin’ role.
Press any key to continue . . .

Option: 2

(you can explicitly choose which user/group is to be given sysadmin role, after you get yourself added in OS admin group)

Above is a manual method .. which has the same steps .. ie. Putting instance in single user and then adding a group or user to sysadmin role ..

enjoy 🙂


Categories: General, T-sql