SQLServerCentral / SQL Server 2005 / Administering

STATISTICS in MS SQL

parthi-1705 — Fri, 20 Nov 2009 23:12:18 GMT

HiWhat is STATISTICS in MS SQL ?What is CREATE STATISTICS?what is UPDATE STATISTICS?DBCC SHOW_STATISTICS ?SP_updatestatsWith this how we can optimize our query or time consumption SET STATISTICS PROFILE { ON | OFF }?SET STATISTICS IO { ON | OFF }?SET STATISTICS TIME { ON | OFF }?pls help me in this.Thanks

Performance tuning

syedkamranfaisal — Thu, 19 Nov 2009 09:19:22 GMT

Dear All,I need a help, can anybody tell me how to increase db performance.Please tell me the performance tuning parameters and their best values.I have a MS SQL2000 Enterprise Edition with 4 GB RMS and there is only db that is 5 GM in the size but whenever I check in Task Manager where sqlservr.exe using the approx 1.6 GM RAM.I can't understand why it using that much memory.Please assist.Regards,Syed Kamran Faisal

Table insert performance ... please help!

scott williams-465021 — Thu, 19 Nov 2009 11:33:58 GMT

I have the following table;CREATE TABLE [dbo].[Journal]( [uuid] [varchar](50) NOT NULL, [message_id] [varchar](50) NOT NULL, [message_version] [varchar](20) NOT NULL, [message_type] [varchar](20) NOT NULL, [direction] [varchar](3) NOT NULL, [processing_code] [char](1) NOT NULL, [event_time] [datetime] NOT NULL, [sending_app] [varchar](45) NULL, [sending_facility] [varchar](45) NULL, [receiving_app] [varchar](45) NULL, [receiving_facility] [varchar](45) NULL, [person_id] [varchar](45) NULL, [person_last_name] [varchar](45) NULL, [person_first_name] [varchar](45) NULL, [person_dob] [varchar](45) NULL, [jcd_path] [varchar](255) NOT NULL, [jcd_service_name] [varchar](45) NOT NULL, [ack] [nvarchar](max) NULL, [message] [nvarchar](max) NOT NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFAfter the table gets rather large - over 100 GB and 12 million rows insert performance becomes incredibly slow, several minutes to insert a record. This seems to happen quite suddenly. After I delete records (which is also really slow) to reduce its size insert performance improves drastically. There are a few indexes on some of the fields and they not fragmented and there is a job to rebuild/reorg them as necessary. This table is used to archive large text data (in message field) as well as storing information about the message.Any ideas on why this is? Thanks for looking!

Attach Database

Tara-1044200 — Fri, 20 Nov 2009 20:06:11 GMT

I did dettach around 300 databases and now i have to attach them to a drive locationD:\SQLDATAD:\SQLLOGIs there a script which queries windows folder to get list of all mdf files and attach them at once instead of manually attaching them.

Index Tunning Tools

peace2007 — Sat, 19 Apr 2008 05:24:40 GMT

Hi, Is there any index tunning tools for MS SQL 2005 better than its own one?Thanks,

TSQL: Select::

John Esraelo-498130 — Fri, 20 Nov 2009 10:47:12 GMT

I have an interesting one..is there a way to suppress the expressions from displaying in a select statement that at the end calculates the total sum...Just want to see the sum / total nothing else..here it is..I have select ..., foobarfrom foobarVilleTablecompute sum(foobar)You may be asking why? I am glad that you asked.. the list is in millions and kind of trying to avoid the verbowse part of that.. :-D

Differences in HA strategies

info-849042 — Fri, 20 Nov 2009 17:28:45 GMT

Hi,I have read about the various forms of SQL Server HA strategies, but I am confused. My questions are:-What benefit does a transaction log backup have over a database backup?-Database mirroring and a failover cluster seem to be the same. In both strategies, you pretty much revert to the standby database if the main production database fails. I say database, but does this go for instance level too?-What scenarios are useful for replication? One I read is to have a database tuned for reads and an identical one for writes. Is there anything else?Thanks

Write cache on Backup Drive

gmamata7 — Thu, 19 Nov 2009 12:17:43 GMT

Hi,We have 3 node a/a/p clustered setup & have SQL Server 2005 EE x64 with SP3 in Windows server 2003 EE x64 and all the drives are on SAN.Node1 - ActiveNode2 - ActiveNode3 - PassiveWe have a backup drive on node1(which has share point databases) and another backup drive on node2 (which has BizTalk databases)We are getting High Disk IO alarm from Monitoring tool Quest Spot light (also from Perfmon), when performing daily full backup for BizTalk databases on node2. After looking at the backup drive properties, I have noticed that Write cache is NOT enabled on SAN. For rest of all drives Write cache is enabled.So my question is:On backup drive, only writes will happen right? so can I enable Write cache on Backup drive? does it makes sense (I'm thinking this will reduce the High disk queue lenght while performing the full backups)thanks

Replication Error - Command Text

hawg — Thu, 19 Nov 2009 14:52:44 GMT

I have posted this over in the Replication forum but it is not even getting looked at yet so I thought I would post it here in case someone might see it quicker because I am having this problem right now.I recently upgraded our only system that performs replication and we had our first replication errors and I have come across a problem to which I cannot find a solution.In SQL Server 2000, whenever an error occurred, I could get the subscription details on the offending article and I could see not only the transaction sequence number and command number but also the actual command text that was being presented for replication.In 2005, I still see the transaction sequence number and command number but I cannot find the command text. I have got to find a way to find this because I need to be able to pull some of the identifying information about the particular data being replicated so I can figure out the proper course of action.Sometimes, due to the way we have our replication setup, we can correct the situation by simply performing an additional update inside the application which allows the replication that is failing to succeed on it's next retry then everything works fine. Sometimes, I am able to identify it as a real error and I can simply skip, or delete, the offending transaction from trying to continue.I have checked all of the distribution tables and the replication tables in both the publisher and subscriber databases and I cannot find the data. I'm beginning to think it is encrypted, for security reasons, so that we can't get to the data directly.If anybody knows of a way to find this, I would be extremely grateful.Thanks,hawg

logshipping db readonly mode

chk2009 — Thu, 19 Nov 2009 15:10:35 GMT

I had setup logshipping for production DB from prod to DR server. Now we are testing ours users to connect to Website thru Dr server DB(stand by mode). throwing error like login can't have access to open database and can't read database.Please advice...I noticed that once did logshipping in secondary server permisssion are not there for login to that database

SQL Server 2005: Upgrade from Standard Edition to Enterprise Edition

MTY-1082557 — Fri, 20 Nov 2009 14:10:59 GMT

I need to upgrade my SQL version in order to create partitions in some tables. I only have one database in production (live). I read some articles where they mentioned I can upgrade the version with:/wait setup.exe ADDLOCAL=SQL_Engine INSTANCENAME=MSSQLSERVER UPGRADE=SQL_Engine SKUUPGRADE=1 /qbI didn’t find any advice regarding a database in production (live). This server is a fail over and I have 2 nodes. Any advice?

SQL Server 2005: Upgrade Error SP2 to SP3

pbyrum — Tue, 10 Nov 2009 14:56:40 GMT

I have a SQL Server 2005 Standard Edition on a 64-bit Server. I was upgrading from SP2 to SP3. After completing the upgrading, the setup reported some componets failed to be installed. Error is:MSP Error: 1316 A network error occurred while attempting to read from the file: <some path to the file>Any suggestions why this occurred? I ran the service pack on the local drive.

User table-Valued Functions

MTY-1082557 — Wed, 18 Nov 2009 13:34:55 GMT

I'm doing a little bit of cleaning in some stored procedures. These procedures are like 20 different versions of reports. They use the same Temp Tables in the beginning of the process. I changed those temp tables for views. And they work well.I have been working with some user table-Valued Functions before. I would like to know if functions will be better instead views or temp tables since the information that I need it’s the same for all the stored procedures. Experienced advice will be appreciated.

Issue connecting to an instance of SQL Express 2005 over peer-to-peer network

k_amalpasha — Mon, 16 Nov 2009 20:10:05 GMT

Hi, I have an instance of SQL Express Server 2005 running on a peer-to-peer network.The server name is KAMAL\SQLEXPRESS when I run sqlcmd -SKAMAL\SQLEXPRESS on the local machine it's installed on i get 1> which is what I should be getting. but when I go to a different PC on the workgroup I am unable to connect to the server, and sqlcmd doesn't give me 1>. I am totally confused about why this is happening.Here are some of the Settings:1.Sql Server Configuration Manager->SQL Server 2005 Network Configuration->Protocols for SQL Express a.Shared Memory - Enabled b.Named Pipes - Enabled c. TCP\IP - Enables d.VIA - Disables2. SQL Server 2005 surface area configuration ->Database Engine -> Remote Connections a. Local and Remote Connections (Selected) i. Using both TCP\IP and Named Pipes (selected)Your Help would be greatly appreciated

Find "lock pages in memory" option

repent_kog_is_near — Mon, 26 Oct 2009 07:57:18 GMT

I was trying to find if "lock pages in memory" option is enabled:I ran exec xp_readerrorlog 0, 1, 'locked pages' and it returns " (0 row(s) affected)"How do you interpret it?ThanksDan

SQL2005 Email Issue

shusta — Fri, 20 Nov 2009 09:30:00 GMT

Hello,Can anyone tell me why I can send all the test emails I want from SQL2005 but never receive any email from my maintenance plan?The maintenance plan has notification enabled and an email account has been selected.Since I can send test emails I’m thinking that I’ve missed something with the maintenance plan.

Can''t open table with SSMS

Curtis Tamanaha — Wed, 01 Feb 2006 09:54:00 GMT

When you right-click on a table it gives you the option to "open table" but it gives me an error, "Object reference not set to an instance of an object. (SQLEditors)"

Do I have something setup wrong?

query plan vs JOIN

raptor — Fri, 20 Nov 2009 11:10:34 GMT

anyone may know how query plan generated the query sequence when use INNER JOIN....which table will be picked as outer table...any input will be very appreciated. I have two tables as belowCREATE TABLE t1 (id INT PRIMARY KEY, COL1 VARCHAR(100))CREATE TABLE T2 (id INT PRIMARY KEY, COL1 VARCHAR(100))GOINSERT INTO t1 VALUES(100, 'TEST1')INSERT INTO T2 VALUES (100, 'TEST2')GOSELECT*FROM T2 INNER JOIN T1 ON T2.ID =T1.ID WHERE T1.ID =100|--Nested Loops(Inner Join) |--Clustered Index Seek(OBJECT:([BESMgmt].[dbo].[t1].[PK__t1__6B8FCA2D]), SEEK:([BESMgmt].[dbo].[t1].[id]=(100)) ORDERED FORWARD) |--Clustered Index Seek(OBJECT:([BESMgmt].[dbo].[T2].[PK__T2__6D78129F]), SEEK:([BESMgmt].[dbo].[T2].[id]=(100)) ORDERED FORWARD) The query plan will use T1 as outer table.SELECT*FROM T1 INNER JOIN T2 ON T1.ID =T2.ID WHERE T1.ID =100 |--Nested Loops(Inner Join) |--Clustered Index Seek(OBJECT:([BESMgmt].[dbo].[T2].[PK__T2__6D78129F]), SEEK:([BESMgmt].[dbo].[T2].[id]=(100)) ORDERED FORWARD) |--Clustered Index Seek(OBJECT:([BESMgmt].[dbo].[t1].[PK__t1__6B8FCA2D]), SEEK:([BESMgmt].[dbo].[t1].[id]=(100)) ORDERED FORWARD)The query plan will use T2 as outer table.why this happens? any suggestion?what i am guessing, SQL Server is picking up whatever it wants...looks like when there is not big different in the data volumn and distribution, and JOIN condition is JOIN clustered index from each table, and condition is filtering clustered index...SQL Server is choosing the last table in the JOIN... i am not sure if I can make this assumption. In a high concurrent environment, which one has been chosen as outer table is critical for resolving blocking and deadlock... if we can get confirmation for this assumption, instead of using force order hint... we can easily switch the join sequence to make T1 as last table in the JOIN, if I always want T1 as outer table......any idea?

Agent job fails: “Logon failure: unknown user name or bad password”

Maxer — Fri, 20 Nov 2009 10:34:02 GMT

I posted this to the ASK SSC part of the site but I'm thinking perhaps this isn't a simple answer but more of a discussion issue so I'm posting it here now if that is OK.My ASK posting: http://ask.sqlservercentral.com/questions/2036/sql-08-agent-powershell-job-fails-logon-failure-unknown-user-name-or-bad-passwAnyway, the issue (on SQL 08):I get the following error:[i]"reason: Error authenticating proxy domain\user, system error: Logon failure: unknown user name or bad password."[/i]This happens when I start my SQL 2008 Agent job that I created.The job is owned by my domain account, I have full sysadmin rights on the SQL 08 install.This is running on my local machine (for dev/testing).There is 1 step which runs a PowerShell script: [i]function Remove-OldFiles($folder, $logFile, $filter, $age) { Get-ChildItem $folder* -Include $filter -Exclude $logFile | ? { $_.LastWriteTime -le (Get-Date).AddMinutes(-$age) } |Remove-Item -verbose } Remove-OldFiles 'c:\PSHtest\' 'DeleteLog.txt' *.txt, *.xml 3[/i]It runs as a proxy account.This proxy account is allowed to use the PowerShell subsystem only and as my domain account is part of sysadmin I have not assigned it to the proxy account principals list.The proxy account uses a credential that is just my own domain account.If I use the SQL Agent account to run the script the job does NOT fail, but the script itself cannot execute and returns errors about it not being able to access the files, but I think that is a permissions issue with SQL agent just not having access to that part of the c:\drive.I could adjust the agent account, but wasn't going to mess with it too much as right now it is just running as LocalService.Though I do wonder about that as now that I think about it, isn't LocalService basically an admin account?Either way, I assigned it to run as a proxy as I will want this to run over the network when I get it working.Which is why I assigned a proxy to run, a proxy that has access to that part of the network for testing.Suggestions on how I can correct this?I've seen pages that say this error may be due to the service account not being enabled for delegation. Could that be the cause of this problem?I am not familiar with the details of delegation so if that is the reason behind it a brief explanation would be greatly appreciated.Thank you!

Script to Change Collation of User Database

lokhande.deepali — Wed, 18 Nov 2009 03:23:30 GMT

Hello,Is their is any Script to change collation of user database ?The process Which I know;First Backup the User database1.creation of new database with required collation (create database..with collate clause)2.Then use of Import & export functionality to move data to newly created database3.Drop the old database4.Rename the newly created database.Wheather this method is the only way ? or any suggestion.Rd,Deepali

How to stop snapshot generation when creating new publication

sqllearner-339367 — Fri, 20 Nov 2009 06:16:47 GMT

Hello Everyone,Is there a way to stop creating the snapshot when creating new publication?Regards,Vidhya

Tempdb new datafile creation date

VRR — Fri, 20 Nov 2009 08:42:30 GMT

Hi,Someone added a new datafile (ndf) to tempdb. I want to know the date of this new datafile. This created a space issue in one of our Server drive.Appreciate your inputs. Thanks!

decoupling systems

Richard Banks — Fri, 20 Nov 2009 05:35:17 GMT

Hi We have 2 sqlservers that are linked with one pushing information to the other. Seems like a tightly coupled solution to me. I'd rather change this to a loosley coupled solution, but am not sure on the options. We have a low tolerance to latency within our company so the solution has to take this into considerationThanks guys

Snapshot Agent Schedule issue in SQL 2005 Replication

sqllearner-339367 — Thu, 19 Nov 2009 03:52:51 GMT

Hello Everyone,Yesterday, I have scheduled the snapshot agent for publication to run everyday at midnight. Then I realised that I dont need to run them everyday, I, have deleted the publication and created new one with option "Create a snapshot immediately and keep the snapshot available to initialise the subscriptions" . However job created shows on schedule (description) "Occurs on 18/11/2009 at 00:00:00" and I'm getting the error on my application log frequently stating "Cannot perform bulk copy on the table".To investigate this further , I went and created another new publication with immediate snapshot creation and found that job properties shows on schedule (description) occurs on 19/11/2009 at 00:00:00). Im assuming that SQL server has not removed my initial setting of running the job everday at midnight.Only possible solution Im aware is to mark the end date on schedule the snapshot agent dialog .Is there any other solution possible?Ta

SQL Server SP 3 CU 6

eric-568297 — Fri, 20 Nov 2009 08:33:58 GMT

Hi all.I've installed SQL SP 3 CU 6 on a database in test, and it broke the ODBC connection. Rolled it back, and it was fine. Any body else have any problems like that ? It was installed on SP 3 with no CU.Thanks.Eric

Identity Error

khushbu — Fri, 20 Nov 2009 04:29:14 GMT

I face strange problem..My SP is selecting from DB1 and inserting into DB2I have no tables which has identity column, neither in DB1 or DB2But when I execute the sp it gives me error Cannot insert explicit value for identity column in table '<table name>' when IDENTITY_INSERT is set to OFF.Can some one please help . Its little urgentThanksKhushbu

Symantec Backup Exec agent

jymoorthy-1100764 — Tue, 10 Nov 2009 22:34:08 GMT

Hi,We have 5 sql servers runs on windows 2003/SQL 2000/SQL 2005 environments. We do have our own maintenance plans. We do push the backups to SAN and then to tapes.One of the vendor somewhat convinced our IT manager to install Symantec Backup Exec on our SQL servers to take backup. I told them No. but i want to hear the gotcha on the agents installed on SQL server. Our business is 24 hrs and heavy OLTP application.Or, is it good to have the agents installed? will it help me in day today maintenance?Thanks in advance.

Network-related or instance-specific error - Connection Medium Problem

ian-1024253 — Thu, 19 Nov 2009 06:33:47 GMT

Hi guys, I know there's hundreds of topic about this error out there, but not a single one seems to apply to my specific situation. [quote]A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)[/quote]We have three branches, 1 head office and two satellite branches. Our in-house developed application connects to a SQL Server 2005 instance running on a server with a live IP which the branches connect through and a local IP that the head office connects through i.e. app at branches connect through <live IP>\<instance name> and the head office connects through <local IP>\<instance name>. The application is exactly the same across all three branches except for the connection string used...it's exactly the same for both satellite branches though. Head office has no problems connecting with the application. Branch A has no problems either. Branch B however does. Branch B's network is setup as follows...ADSL connection into a wireless/wired router that each user then gets internet access through. If anyone in Branch B connects to the internet through 3G or their cellphones on their PCs, then the application works. But not through the ADSL setup. However, to make it more interesting...one of the guys made an initial connection to the internet using his cellphone as the modem and successfully logged into the application...he then disconnected the internet from his cellphone and connected through a cable connection to the ADSL router and successfully managed to carry on utilising the application, performing actions that would have performed SQL queries to the server...and all ran successfully. But then he closed the app and opened it again (cable to router connection) and again login failed.Please any ideas.

Merge multiple datafiles and split them intelligently on storage...

pdevadiga — Fri, 20 Nov 2009 06:43:03 GMT

Hi,I have a client who is running a production DB ABC around 250 GB and I found that the DB has 3 datafiles. This is a database on SQL SERVER 2005 running a Microsoft ERP.ScenarioThe files which I mentioned above is as follows:1) ABC.mdf size 500 MB2) ABC_1.ndf size 120 GB3) ABC_2.ndf size 80 GB4 ABC_3.ndf size 50 GBI found that all these files belong to the same filegroup.Now they have bought a storage having 24 HDD of 146 GB each. Now the problem or rather a question.From above I could analyze that someone has made these extra datafile without any knowledge as to why they should be doing it. What I would like to do is merge all these datafiles into one big file (ABC_1.ndf file because the ABC.mdf file cannot be tampered with ) and then create different filegroups on seperate allocated RAID assigned disks from the storage (for e.g 4 x 146 GB HDD configured as RAID 1+0) and move the heavily accessed transaction tables into it. And then move master tables into different RAID partition....and so on.What steps I need to take to accomplish the same ?What precautionary measures do I need to take before doing the same ?Approximately how much time it would take me to accomplish the exercise?(Sometime back I had emptied one datafile around 50 GB to a 100 GB datafile and it took almost 2 days to finish ...Can there be a simpler way to do this because I cannot afford more than 3-4 hours downtime)Can someone enlighten me as to how do I go about it.The server config is HP DL580 G5 with 4 Quad core procs and with 32 GB RAM with 6 internal 146 GB HDD.Your comments and valuable inputs will be appreciated.Regards,Prashant

Distributor performing Bulk copy for every minute in SQL 2005 Replication

sqllearner-339367 — Thu, 19 Nov 2009 17:05:32 GMT

Hello There,I have transaction replication with distributor running continously. Snapshot is generated with immediate initialization option. However, I can see errors stating "Cannot perform Bulk copy" on my subscriber/distributor for every minute. I have cross checked that snapshot is generated only once (ie in transaction replication) and there are no more schedule other than distributor agent. I have 5 more replication with same setting running continously. After some investigation it is found that sp_msacquiresnapshotdeliverysession_lock stored procedure has been in held in shared lock by more than 3 process causing the dead lock.Have any one come across this issue?.Any suggestions?Ta

database mail sending error

raj acharya — Fri, 20 Nov 2009 02:43:52 GMT

hi,i am getting following error Msg 14641, Level 16, State 1, Procedure sp_send_dbmail, Line 81Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail.as per this i did exec sysmail_start_spthat time it's show me status started but when again i start to send it's showing me same error againany one has any suggestion or solution then let me know asap

Security profiling

tvantonder-992012 — Wed, 18 Nov 2009 05:06:05 GMT

Good dayI need to profile users and the rights they need on a server. I want to reduce the rights given to them so that each user has min rights to function correctly.I am running traces at this moment to check which users is using which databases but I do not know if there is a way of collecting object used and what commands executed by the users. I have over 3000 users in the enviroment so manually checking each user is not an option.If anyone have a suggestion or two no matter how small it will be appreciated.

How to set cluster to not start sql if resource is offline

Indianrock — Thu, 19 Nov 2009 07:56:34 GMT

Since those of us acting as DBAs don't have access to the cluster administrator tool, I'm wondering how we can tell ( from event logs etc ) if it's been set so databases and sql server itself cannot start until all resources are online. We have a sql 2005 Enterprise two-node cluster ( active/passive) with a NetApp for storage. We've added 8 new luns as mount points and are about to start splitting data files, migrating tables with IO problems to the new luns. It's critical that the database not be brought online and the sql service not start until these are available. Our Systems Admin states that he's done the dependencies in Cluster Administrator and tested failovers, but I'm not sure anything I have access to ( event and sql logs ) is showing sql failing to start. I'm now looking at the cluster.log I found in the windows folder on one of the nodes.I may look into a vbscript that will read active directory ( \root\MSCluster ) using WMI as I was able to use this approach recently to keep track of users contained in Domain Groups that have sql access.

why i can't shrink tempdb data file

Mad-Dog — Mon, 27 Apr 2009 12:08:03 GMT

Hi,my tempdb database is 30GB(datafile).i try to shrink it with all those options but no luck.use tempdbgoDBCC SHRINKFILE (tempdev,5000)goDBCC SHRINKFILE (tempdev,truncateonly)goDBCC SHRINKDATABASE (tempdb,5000)--answerDBCC SHRINKDATABASE: File ID 1 of database ID 2 was skipped because the file does not have enough free space to reclaim.DBCC execution completed. If DBCC printed error messages, contact your system administrator.sp_spaceused--answertempdb 29971.25 MB 29219.77 MBDBCC OPENTRAN--answerNo active open transactions.DBCC execution completed. If DBCC printed error messages, contact your system administrator.i run this as well with no luckDBCC FREEPROCCACHEGODBCC DROPCLEANBUFFERSgoDBCC FREESYSTEMCACHE ('ALL')GODBCC FREESESSIONCACHEGOdbcc shrinkfile (tempdev,5000)gohow i can shrink the file without restart the sql server services?THX

DBCC SHOWCONTIG PERMISSIONS

ramuvanparti — Fri, 20 Nov 2009 00:48:21 GMT

I WANT TO GRANT PERMISSION FOR DBCC SHOWCONTIG PLEASE TELL HOW TO DO THAT

Moving Test DB to Live Environment

Steve Hindle-535638 — Wed, 18 Nov 2009 08:51:02 GMT

I have a test database that I have made changes to and is ready for deployment into live. What's the best way of doing this, while still retaining all the data in live?For example:1) backup live database and restore to a temporary area 2) generate and execute scripts from test database to create new live empty database3) develop SSIS package to populate the new structure with live data from the temporary copy of the live database?Any ideas to simplify this process would be apprciated.Thanks,Steve

Daily health checks for SQL Server

Panks-913490 — Thu, 19 Nov 2009 03:22:29 GMT

Hi,I have been to put up some daily health scans on our SQL Server. Can some one please suggest some useful scans and how to create them.thanks

backup scripts

sql.pravin — Thu, 19 Nov 2009 17:03:44 GMT

hi everyone,i need a script (stored proc ) to identify all user databases in a server and then apply full backup, differential backup and transactional backup to them ( with their specific schedules )and it should automatically delete all the backups keeping 2 recent backups in all the 3 types(full, diff, tran)thanks,praveen

Maintenance Plan is failing

mahesh.vsp — Wed, 28 Oct 2009 04:09:26 GMT

Hi,I have Maintenance plan created on the database server which basically takes a backup daily and deletes the previous days backup file.Also, the log files are getting generated in D:\Program Files\Microsoft SQL Server\MSSQL\LOG folder.I can see all the log files which are getting generated .I have pasted the log file which has generated on 10/27/2009 and 10/28/2009.IF you see at the end of log1 , it clearly says unable to delete the backup files.Similarly, if you see the end of log2 , it says the old file is deleted successfully.The problem is, every alternate day the job is getting failed and we are getting and receiving an email saying that the backup has failed, but the actually the backup is being done successfully but was unable to delete the preivous day backup (RETENTION PERIOD = 1 DAY).How to trouble shoot this issue. One its working fine able to delete the prvious day bkp, but on the next it is unable to delete the previous day backup!!!!! Can anyone figure out why it is happening so. Also, find the attached screen shot for RETENTION period of 1 day.In the Notification section/TAB of the job, we are logging into Windows Event viewer incase of job failure.But i cannot see any log entry in the Event Viewer. This is obvious, because the job contains three 3 steps ,and it is getting Failed at step1 which fails at deleting the backup and goes to step3 which sends an Email saying "backup failed" ====================================log1 generated on 10/27/2009====================================Starting maintenance plan 'All Application DBs' on 10/27/2009 9:30:00 PM[1] Database ActionOI_CDI: Database Backup... Destination: [E:\SQLDUMP\CDI_db_200910272130.BAK] ** Execution Time: 0 hrs, 0 mins, 1 secs **[2] Database ActionOI_CDI: Verifying Backup... ** Execution Time: 0 hrs, 0 mins, 1 secs **[3] Database ActionOI_CG: Database Backup... Destination: [E:\SQLDUMP\CDI_db_200910272130.BAK] ** Execution Time: 0 hrs, 1 mins, 37 secs **[4] Database ActionOI_CG: Verifying Backup... ** Execution Time: 0 hrs, 0 mins, 38 secs **[15] Database ActionOI_Template: Delete Old Backup Files... Unable to delete file E:\SQLDUMP\CDI_db_200910262132.BAK. 0 file(s) deleted.Deleting old text reports... 1 file(s) deleted.End of maintenance plan 'All Application DBs' on 10/27/2009 9:42:28 PMSQLMAINT.EXE Process Exit Code: 1 (Failed)================================log2 generated on 10/28/2009===============================[11] Database ActionOI_CDI: Delete Old Backup Files... 1 file(s) deleted.[12] Database ActionOI_CG: Delete Old Backup Files... 1 file(s) deleted.[13] Database ActionOI_Queue: Delete Old Backup Files... 1 file(s) deleted.[14] Database ActionOI_RptLog: Delete Old Backup Files... 1 file(s) deleted.[15] Database ActionOI_Template: Delete Old Backup Files... 1 file(s) deleted.Deleting old text reports... 1 file(s) deleted.End of maintenance plan 'All Application DBs' on 10/28/2009 3:00:09 AMSQLMAINT.EXE Process Exit Code: 0 (Success)Below is the script which i have generated from GUI DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance' -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'DB Backup Job for DB Maintenance Plan ''DB Maintenance Plan ActionOI_Template''') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- There is, so abort the script RAISERROR (N'Unable to import job ''DB Backup Job for DB Maintenance Plan ''DB Maintenance Plan ActionOI_Template'''' since there is already a multi-server job with this name.', 16, 1) GOTO QuitWithRollback END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'DB Backup Job for DB Maintenance Plan ''DB Maintenance Plan ActionOI_Template''' SELECT @JobID = NULL END BEGIN -- Add the job EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'DB Backup Job for DB Maintenance Plan ''DB Maintenance Plan ActionOI_Template''', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job steps EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID FC8F5D33-E6BD-4E61-BEAD-6BD563620322 -Rpt "d:\Program Files\Microsoft SQL Server\MSSQL\LOG\All Application DBs4.txt" -DelTxtRpt 4DAYS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "E:\SQLDUMP" -DelBkUps 1DAYS -CrBkSubDir -BkExt "BAK"''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 2, @on_success_action = 4, @on_fail_step_id = 3, @on_fail_action = 4 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'SendSuccess', @command = N'sendemail -f healthcare.prod@ABC.com -t babu@ABC.com;RAM@ABC.COM -cc XYZ@abc.com;traj@abc.com -u Backup Succeeded on DBSERVER01 -m Backup has been completed successfully -s 192.168.1.103', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'SendFailed', @command = N'sendemail -f healthcare.prod@ABC.com -t babu@ABC.com;RAM@ABC.COM -cc XYZ@abc.com;traj@abc.com -u Backup Failed on DBSERVER01 -m Backup job failed please check -s 192.168.1.103', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job schedules EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 20060410, @active_start_time = 213000, @freq_interval = 126, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the Target Servers EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ENDCOMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: Thanks in advance

Save to File

ss1011 — Thu, 19 Nov 2009 18:06:43 GMT

Hi,I'm trying to enable the save to file option when configuring profiler. It is enabled, however, when I check it, it immediately unchecks it. I'm using 9.0.4035 with the sa account.What do I need to do / set to get this to work?Thanks in advance.update: OK - pretty retarded but you have to run the trace first, then save it, then the properties will allow you to save to file going forward.I wonder if that's a certification question.