sqlcmd Switches
-S >> ServerName
-D >> DatabaseName
-U >> UserName
-P >> Password
-Q >> Query
-o >> OuputFile
sqlcmd -SSQL2005_FIRST -Ucalyan -Pcalyan
-dDatabaseA -Q"select * from sampledata"
sqlcmd -SSQL2005_FIRST -Ucalyan -Pcalyan
-dDatabaseA -Q"select * from sampledata" -o"c:\sampledata.txt"
sqlcmd -SSQL2005_FIRST -Ucalyan -Pcalyan
-dDatabaseA -Q"EXEC MYPROC1"
sqlcmd -SHOME-CB\SQL2005_FIRST -Ucalyan -Pcalyan
-dDatabaseA -Q"exec msdb.dbo.sp_start_job @job_name = 'TestJob'"
Saturday, June 26, 2010
Tuesday, June 1, 2010
sys.indexes vs sysindexes -- Explanation
Generally everybody use both the commands, here is the explanation.
sysindexes -- compatibility views
sys.indexes -- catalog view
Generally we should avoid using sysindexes kind of compatibility views. Microsoft keep this views is becoz to make backward compatibility while migrating from one version to another version.
sysindexes -- compatibility views
sys.indexes -- catalog view
Generally we should avoid using sysindexes kind of compatibility views. Microsoft keep this views is becoz to make backward compatibility while migrating from one version to another version.
Dont use count(*) -- Not good practice
“Select Count (*) From table”.
The problem with this instruction is that most of the times it performs a Table or Index Scan to return the amount of records in the table. For large tables this is a synonymous of slow query and high consumption of server resources.
Use the following query to find out rows in a table.
SELECT rows FROM sysindexes
WHERE id = OBJECT_ID('table_name') AND indid<2
The problem with this instruction is that most of the times it performs a Table or Index Scan to return the amount of records in the table. For large tables this is a synonymous of slow query and high consumption of server resources.
Use the following query to find out rows in a table.
SELECT rows FROM sysindexes
WHERE id = OBJECT_ID('table_name') AND indid<2
Set nocount on Best Practices
While writing any stored procedure make use of set nocount on.
Select, Insert, Update and Delete you must have already seen the “nn row(s) affected” message making part of the result of your query
But this message may generate a great impact in the performance of your Stored Procedures
The Set NoCount option disables the sending of these messages. It will increase the performance while executing SPs over the network
Select, Insert, Update and Delete you must have already seen the “nn row(s) affected” message making part of the result of your query
But this message may generate a great impact in the performance of your Stored Procedures
The Set NoCount option disables the sending of these messages. It will increase the performance while executing SPs over the network
Error: 14420, 14421, Severity: 16, State: 1 LogShipping
The log shipping primary database %s.%s has backup threshold of %d minutes and has not performed a backup log operation for %d minutes. Check agent log and log shipping monitor information.
WorkAround
1. The Message 14420 does not necessarly indicate a problm with logshipping. And this message mostly occur when monitor server is configured. This message generally occured when the differnce between t-log backup and the current time on the monitor server is the greater than the time is set for backup threshold.
2. Ensure the transaction log backup happend on the primary server. If the t-log backup fails then also above error will occur.
3. You may set incorrect value for the backup alert.
4. The date and time of monitor server is different from the date and time of primary server.
5. The logshipping copy job is run on the primary server and may not update the entry in the msdb database at monitor server in the log_shipping_primaries table.
14421 Error :
The log shipping secondary database %s.%s has restore threshold of %d minutes and is out of sync. No restore was performed for %d minutes. Restored latency is %d minutes. Check agent log and logshipping monitor information.
The message doesn't necessarly indicate a problm with logshipping.
1. This may occur restore job on the secondary server is failing.
2. You may set out of sync alert is wrong.
WorkAround
1. The Message 14420 does not necessarly indicate a problm with logshipping. And this message mostly occur when monitor server is configured. This message generally occured when the differnce between t-log backup and the current time on the monitor server is the greater than the time is set for backup threshold.
2. Ensure the transaction log backup happend on the primary server. If the t-log backup fails then also above error will occur.
3. You may set incorrect value for the backup alert.
4. The date and time of monitor server is different from the date and time of primary server.
5. The logshipping copy job is run on the primary server and may not update the entry in the msdb database at monitor server in the log_shipping_primaries table.
14421 Error :
The log shipping secondary database %s.%s has restore threshold of %d minutes and is out of sync. No restore was performed for %d minutes. Restored latency is %d minutes. Check agent log and logshipping monitor information.
The message doesn't necessarly indicate a problm with logshipping.
1. This may occur restore job on the secondary server is failing.
2. You may set out of sync alert is wrong.
spid SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
This issue is because of Autoclose Option
Today i got a screenshot from one of our production servers that database is starting up frequently in the error log file. The following error find in the error log :
spid97 Starting up database 'MyDb'.
spid16s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
I tested the following error and came to know what is happening exactly
WorkAround:
1. Generally this situation will come when we restore database from another server. When a database has been restored from another server it clears the existing plan cache.
2. To avoid this situation we need to set Auto_close option to Off / False.
If auto_close option is true it will freeup the resources when all the users disconnected from the database, but in the production environment we can't give guarantee that next second other user will not connect to database, becoz of this reason database will give startup message in the error log file, and it is a performance overhead.
Auto_close option set to be false to enable database mirroring also.
Auto_shrink option also set to be false.
Today i got a screenshot from one of our production servers that database is starting up frequently in the error log file. The following error find in the error log :
spid97 Starting up database 'MyDb'.
spid16s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
I tested the following error and came to know what is happening exactly
WorkAround:
1. Generally this situation will come when we restore database from another server. When a database has been restored from another server it clears the existing plan cache.
2. To avoid this situation we need to set Auto_close option to Off / False.
If auto_close option is true it will freeup the resources when all the users disconnected from the database, but in the production environment we can't give guarantee that next second other user will not connect to database, becoz of this reason database will give startup message in the error log file, and it is a performance overhead.
Auto_close option set to be false to enable database mirroring also.
Auto_shrink option also set to be false.
Subscribe to:
Posts (Atom)