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 3 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


Could not find database ID 2, name ‘tempdb’. The database may be offline

September 14, 2013 Leave a comment




one of my SQL server .. has been logging in ERRORS related to Tempdb been unavailable during Startup and after few seconds everything


seems to be working fine .. i assumed that its related to drives holding the TempDB was going offline or it was not accessible when the SQL


was coming online .. below is the error message 




013-09-10 06:36:22.850 spid55 Could not find database ID 2, name ‘tempdb’. The database may be offline. Wait a few minutes and try again. 2013-09-10 06:36:22.980 spid9s Starting up database ‘tempdb’.


After some time the SQL would recover normally .. without any furthur errors or similar messages 


Solution :


i figured out that this is a known issue and does occur in SQL 2000 and 2005 (could also be a possiblity in higher versions too)


below is what is causing this 


Application like  Biztalk or web Farm keep trying to connect to SQL every second while the sql is starting up .. 


and they try to Create objects in tempdb .. from the very go … as TEMPDB is been refreshed and not accessible  above mentioned messages


are logged in SQL error log .. (which can lead to DBA’s looking for reason from drive prespective)


–> i have used TRACE FLAG 3614 to cause the SSNETLIB.dll (SQL server network library) to be loaded only after successful recovery of


system databases and hance above messages are not logged in SQL error log .. 


refer :




load test you SQL server CPU

August 24, 2013 Leave a comment

Hey There ..

                      I found 2 great CPU load testing tools .. that you can use to run heck your SQL server CPU ..

                      here’s what I got from them (download it from below link )


                     GEEKBENCH 3 can be downloaded from above link .. and below are some of the test it run against the CPU and

                      memory .. the results are very detailed and easy to consume ..


                       once you start the benchmark test .. you will see different load been put on CPU and memory ..


                       you will observer .. that CPU and memory are going over the roof .. and wish to figure out other ways to do

                       the  same …

Geekbench_3                    the results are informative about what is and what counter and numbers our server resources have hit


                     the 2nd great tool .. that we will discuss is available for download from below link ..


                    once you are done with download .. and get it installed .. you will get a screen similar to below ..


the different tabs on above screen cover all aspect of processor .. that you would be interested in finding out ..

so enjoy and play around with these free tools and spread the word ..

Thanks for reading .. 🙂

bonus video by

Understanding CPU Specifications DalePoston

great whitepapers on Contention in SQL Server

August 24, 2013 1 comment


Hey .. Check out 2 great whitepapers on contention in SQL SERVER ..


Diagnosing and Resolving Latch Contention on SQL Server


Diagnosing and Resolving Spinlock Contention on SQL Server

Enjoy 🙂


SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison

August 10, 2013 Leave a comment

Difference Between Union vs. Union All
Great Post by Pinal Dave ..
Enjoy 🙂

Categories: INDEXES