Archive

Posts Tagged ‘SQL’

Database Page Basics

December 27, 2013 Leave a comment

Hi all,

i was looking for a some basics to find out to which Table a particular page belongs to ..

Found below you tube Channel which covered everything i was looking for

check out the link and subscribe to the same ..   SQLpassion by  Klaus Aschenbrenner 

 you will cover below basics if you try out the example yourself ,

1>basic Database  PAGE Structure  and how to use DBCC IND , DBCC PAGE  and DBCC TRACEON(3604)

2> how the table partition and allocation units are related each other

3> what are different types of Allocation units  i.e in row ,LOB and row over-flow data

   Please try the example as you go through the the links 

   1 

   2

3

4

Enjoy 🙂

HOW TO Set up TEST Environment for SQL SERVER 2014 CTP2 or SQL SERVER 2012 PART 2

December 23, 2013 1 comment

Hi ALL,

for setting up TEST env for Server 2012 and above refer to my First post for initial details and videos from 1 to 6

remaining Videos are as below :: –>

once again CREDITS goes to metamanager  (Check out the Channel and Subscribe to the same)

Video number 7 

Video number 8 

 Video number 9

Video number 10

 

CREDITS goes to metamanager 

Thanks a lot for checking out the page .. do comment and share !!!

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
DECLARE @SQL VARCHAR(256) — SQL command

 

 

DECLARE db_cursor CURSOR FOR
SELECT 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

 

 

WHILE @@FETCH_STATUS = 0
BEGIN
–SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName

 

 

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

exec (@SQL)

 

 

FETCH NEXT FROM db_cursor INTO @name
END

 

 

CLOSE db_cursor
DEALLOCATE db_cursor

 

 

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

 

DBCC_CHECKDB_ALL

 

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

ENJOY 🙂

 

 

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 

 

ERROR :

 

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 : http://support.microsoft.com/kb/834798

 

 

 

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 )

                       http://www.primatelabs.com/geekbench/

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

Geekbench_1

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

Geekbench_2

                       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

Geekbench_4

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

                     http://www.cpuid.com/softwares/cpu-z/versions-history.html

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

CPU-Z_2

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

 

http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/07/05/diagnosing-and-resolving-latch-contention-on-sql-server.aspx

 

Diagnosing and Resolving Spinlock Contention on SQL Server

 

http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/07/05/diagnosing-and-resolving-spinlock-contention-on-sql-server.aspx

Enjoy 🙂

 

using powershell to select from Tables in database

July 28, 2013 Leave a 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

PS1_1

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

connect_2

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

connect_7

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 ..as 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”

connect_3

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

connect_4

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 …

Enjoy:)