Posts Tagged ‘Database’

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





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


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 🙂


getting Started with Execution plan – part 1

August 4, 2013 Leave a comment

Hi There,

today we will see ..ways to retrieve estimated Execution plan through different option available ..

I’m using 2008 R2 Adventure works database which is below  available link

(for other downloadable database option use THIS)

execution plans can be either estimated or it can be actual execution plan ..

     estimated execution plan :

its how the optimizer thinks will be the best possible way to implement a particular query .

which operators and in what sequence to access the tables …

so the query completes in fastest possible time ,having lowest cost

COST –> CPU cost + IO cost

actual execution plan : is how the storage engine actually executed the query it got from relational engine

relational engine passes on the estimated execution plan to the storage engine …

which uses it as reference to execute the submitted query by user …

Actual execution plan may differ due to the fact .. that statistics are out

of date, execution of some part of query caused recompilation or the

storage engine choose to execute the query in parallel mode …

estimated execution plans are stored in plan cache from where it can be reused …

different ways to retrieve execution plans

A. while we access/retrieve  the estimated execution plan ..

the query is never actually executed ,only estimated

plan is produced by the optimizer..

ways to get estimated execution plan

 1> set Showplan_all ON  (give a text based output of estimated execution plan .. has quite a information )

set Showplan_all OFF (to switch off)


  2> SET  SHOWPLAN_text ON (gives a text based estimated execution plan ,which has lesser information )

compared to  Showplan_ALL,but this is usefull for tools like oSQL.exe which will ready consume the output

set Showplan_Text OFF


3> set Showplan_XML ON (to get the XML based execution plan which much more detailed)

set Showplan_XML OFF

once you click on the SHOWPLANXML link in the result tab .. you will below screen .. with more tooltips






             4> forth option to get the estimated execution plan is SSMS based .. you need to click a button within GUI or

go to SSMS – > Query -> display estimated execution plan or Control + L


in the next post .. we will see ways to retrieve actual execution plan ..

im some cases actual plan may differ from estimated execution plan ..

Thanks for reading ……. ENJOY 🙂

using powershell to select from Tables in database

July 28, 2013 1 comment

I was looking for some ways to connect to connect to SQL instance .. Sarabpreet had covered quite interesting ways to do the same … you may check his article over here (a big Thanks to Sarab for sharing the same …)

below is what I tried on windows 7 (SQL 2008 R2 )

once we are within shell .. shell treats all objects with SQL

as files and folders … like what we have while we are working with CMD

we can use different ways to connect to sql server via PowerShell tools ..PS1

1> type powershell.exe   in the start –> run prompt and hit enter

this will keep you PowerShell prompt

2> type SQLPS.EXE   in the start –> run prompt and hit enter

3> or right click on any tab in the SSMS and click on Start PowerShell

depending on were you have right clicked .. you be taken to appropriate

depth or hierarchy with in the SQL instance


for this example we will use step 3 …

refer the snapshot on the right …

if you right click on any of the object within the SQL instance .. we will

taken into the corresponding hierarchy level within the SQL

once we are within the database .. we will use command DIR .. to look for

list of folder and files within (comparing the database structure to OS file system )

Note :: after a certain level command will not return any results or output …

below is an example of a command ran at database level .. we can all objects that are available in SSMS for  a database


we will need to use a SQLPS command named INVOKE-SQLCMD to get any meaning full data out of the SQL server ..

once OS based commands like dir (which will work same as LS and get-childitem) hits its limits ..

1> get-help invoke-sqlcmd -FULL

2> get-help invoke-sqlcmd -EXAMPLE

3> get-help invoke-sqlcmd


invoke-SQLCMD has multiple parameters which help use to define query and connection options

so once we are within correct location (use cd or cd .. to move from one path to another .. )

we will select data from a table below  .. command used is as below (we may try it on any table with some data in it)

invoke-sqlcmd “select top 10 * from dbo.T2 where id > 1000”


below is an example of using SQLCMD to run another command to populate data ..

so you can run any t-sql command in SQLPS shell.. that use can run in SSMS


so what is use of all this .. if we can run these commands in SSMS … well the power of powershell

really comes across when we have list of servers to manange .. and we want to automate some admin

tasks across all of them …. going forward I will share more examples on Powershell …


Check out SQLSERVER-Performance-Tuning blog

July 7, 2013 Leave a comment

Hi All,New start to start

Check out below Link for all latest trends and news on SQL SERVER Performance

tuning ,its an unique efforts in GULF and MIDDLE EAST ,

do join the subscription list and stay updated with latest blog-post ,,, ENJOY 🙂


SQL Server Performance Tuning is a specialized SQL Server community fully of several rich blogs ,videos tutorials , offline and online event for widely concerned topics on Microsoft SQL Server  like T-SQL performance optimization , Data Warehousing solutions, Replication Solutions , DMV library ,new SQL Server 2012 features ,index tuning , heavy locks and deadlocks   ..etc