Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James' SQL Footprint

Love SQL Server, Love life.

Backup Master Database

The story came from a question someone asked me.

Does Master database support full recovery mode?

As I remembered, by default, Master database is in "Simple" recovery mode. I never try to put it into Full recovery mode because master database is not updated frequently, and it is very small.…

Read more

0 comments, 1,964 reads

Posted in James' SQL Footprint on 7 July 2012

New in SQL 2012 (1)

Learning new is a interesting thing. Today I read several posts regarding the SQL Server 2012 new feature:

1. Indirect Checkpoint
Prior to SQL Server 2012, SQL Server Database Engine Support 3 type of checkpoints: Automatic, manual and internal. Now there is a new checkpoint type added : Indirect Checkpoint.

Not…

Read more

0 comments, 1,002 reads

Posted in James' SQL Footprint on 1 July 2012

Several useful T-SQL Function

1. Using the OVER clause with aggregate functions
The following sample is from BOL:
-------------------------------------
USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'

Read more

1 comments, 1,085 reads

Posted in James' SQL Footprint on 30 June 2012

Rebuild index with "Alter Index Rebuild" or "DBCC DBREINDEX"

You can rebuild all index for a table with "Alter Index Rebuild" and "DBCC DBREINDEX" .

First, in BOL, for "DBCC DBREINDEX" , it mentions
This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify…

Read more

2 comments, 5,923 reads

Posted in James' SQL Footprint on 23 June 2012

Shrink transaction log file

Sometimes the shrink transaction log file doesn't work.

Before we troubleshoot it, we need to know how the log file works:

Transaction Log Physical Architecture
http://msdn.microsoft.com/en-us/library/aa174526(v=sql.80).aspx

Here are some tools which can help us investigate the log file issues.
a. DBCC SQLPERF(logspace) : tell you how much transaction log is…

Read more

0 comments, 1,162 reads

Posted in James' SQL Footprint on 17 June 2012

Max Server Memory

Sakthivel Chidambaram recently created a calculator which can find out the max server memory value based on the input.
http://blogs.msdn.com/b/sqlsakthi/archive/2012/05/19/cool-now-we-have-a-calculator-for-finding-out-a-max-server-memory-value.aspx

you can try the tool from http://blogs.msdn.com/b/sqlsakthi/p/max-server-memory-calculator.aspx

Then Jonathan Kehayias made a post to clarify why the calculator doesn't work
http://www.sqlskills.com/blogs/jonathan/2012/05/default.aspx
Jonathan has another great post relative the max server memory…

Read more

0 comments, 2,389 reads

Posted in James' SQL Footprint on 2 June 2012

Setup windows firewall for SQL Server

On Windows 2008 R2, the windows firewall will turn on by default which causes connection issure for the remote client, here is a script which can turn off the firewall for all SQL Server default port. if there is a named instance, the script should be modified based on the correct setting.

Read more

1 comments, 2,418 reads

Posted in James' SQL Footprint on 1 June 2012

Backup duration statistics

Here is a simple query which can list backup duration statistics for all database, including the max, min, avg of the backup duration.  you can estimate the backup duration by this query when you plan your backup job



 SELECT
  DBNAME,
  backup_type,
  MAX(sizeMB) as MaxBackupFileSizeMB,
  Max(duration) as MaxDurationSEC,
  MAX(speedMBSEC) as MaxSpeedMBperSEC,
  MIN(sizeMB) as…

Read more

0 comments, 499 reads

Posted in James' SQL Footprint on 25 May 2012

Ad hoc query optimization in SQL Server

When ad hoc queries are executed in sql server, if it is executed without parameters, and it is simple,  SQL Server parameterizes the query internally to increase the possibility of matching it against an existing execution plan, that's called "Simple Parameterization"

For instance:

--clear plan cache first
DBCC FREEPROCCACHE

--run…

Read more

3 comments, 2,904 reads

Posted in James' SQL Footprint on 15 May 2012

Use Powershell script to verify backup files

Before restoring backup, we always verify the backup file first, and run the 3 command below:

1. RESTORE HEADERONLY
    Returns a result set containing all the backup header information for all backup sets on a particular backup device.

2. RESTORE FILELISTONLY
    Returns a result set containing a list of…

Read more

0 comments, 4,056 reads

Posted in James' SQL Footprint on 12 May 2012

Backup database in CMS

Sometime you know the database name which you want to backup, but you don't know which server it is on. Normally we will
1. run query on CMS to find out which server the database is on
2. then connect to the server and run backup query.

If you backup…

Read more

0 comments, 465 reads

Posted in James' SQL Footprint on 9 May 2012

Generate restore script automatically by backup history table

I wrote a store procedure which could generate restore script automatically by backup history table. it can help you select a best restore solution base on the backup history table.

For instance,  if you backup the database as the sequence below
ID backup_start_date backup_type
1 2012-04-30 18:28:43.000 Full
2 2012-04-30…

Read more

2 comments, 2,087 reads

Posted in James' SQL Footprint on 30 April 2012

Setup database mirroring and log shipping together

SQL Server has multiple high availability solutions. Comparing with cluster, database mirroring and logshipping are easier to setup, and they don't need special hardware.

here is a great article about how to setup the database mirroring and log shipping together.
http://technet.microsoft.com/en-us/library/ms187016.aspx

In our system, we setup data mirroring with high-safety…

Read more

0 comments, 3,080 reads

Posted in James' SQL Footprint on 21 April 2012

script system object in resource database

When I troulbshooting the logshipping problem, I found many logshipping store procedure can not be found in the master or msdb database. for instance, in the master. [sys].[sp_add_log_shipping_secondary_primary] , it calls sys.sp_MSprocesslogshippingjob to create the copy job, however, sys.sp_MSprocesslogshippingjob is in the resource database, we can not review its code with sp_helptext command.

Read more

0 comments, 445 reads

Posted in James' SQL Footprint on 19 April 2012

Log shipping setup problem

Last week, our log shipping setup had a problem, so I spent some time to dig into the log shipping.

there are 2 monitor tables after logshipping setup
1. log_shipping_monitor_primary
      This table has entries for all primary database on primary server and monitor server

2. log_shipping_monitor_secondary 
      This table has entries…

Read more

0 comments, 739 reads

Posted in James' SQL Footprint on 18 April 2012

Why I can not connect sql server ???

       Recently, I installed sql server 2012 on windows 7 laptop, today when I wanted to login sql server with TCP/IP, I got error.

1. Enable TCP protocol
I tried to login sql server by SSMS with Server Name "MachineName\InstanceName",  and select the Network protocol "TCP/IP"


but got error message below

My…

Read more

0 comments, 4,158 reads

Posted in James' SQL Footprint on 10 April 2012

Why shrink sql server data file failed?

Today I need to shrink a big data file, I know shrink file is not a best practise, and it will cause data fragment, but sometimes after you clean the database with deleting old data, you had to shrink file, it should be fine as long as you run rebuild…

Read more

2 comments, 970 reads

Posted in James' SQL Footprint on 7 April 2012

Clean SQL Server Cache

           I lives alone, usually, I clean my small apartment at every weekend, wipe the table/firniture with cloth, clean the capet with vacuum cleaner, wash clothes with machine. the clea enviroment makes me feel confortable, and have a good start for the new week.

SQL Server memory cache just like…

Read more

0 comments, 7,170 reads

Posted in James' SQL Footprint on 6 April 2012

SQL Server 2012 license

Not sure if it is a good news or bad news, SQL Server 2012 license shifts from processor-based to core-based.......here are the changes

1. There is no Datacenter Edition anymore, it is merged into Enterpise Edition. Furthermore, Workgroup and Small business Editions are also being retired.

Currently there are only…

Read more

1 comments, 1,116 reads

Posted in James' SQL Footprint on 5 April 2012

Install SQL Server 2012 Book online on local disk

After installing the SQL Server 2012 on my laptop, I found there is only online document available, no local book online document found...........I thought I might not select the local help document during installation, but by researching, it is the new feature of SQL Server 2012..........I don't know what sql…

Read more

6 comments, 9,378 reads

Posted in James' SQL Footprint on 1 April 2012

Newer posts

Older posts