SQL Server Disaster Recovery and Availability Forum

T-SQL Statements to Stop and then restart mirroring

Fri, 27 Nov 2009 11:53:40 Z

Hi All,

I'm endeavouring to create an SSIS package to transfer logins between a principal and backup database in a mirroring scenario. Because the mirroring is enabled, it will not let me copy onto the secondary database. So please can someone tell me the statements to stop and restart mirroring so that I can put them at the start - and end - of the package routine?

I tried pausing / resuming mirroring, but this did not work.

Many Thanks
Graham

SQL Server 2000 Enterprise Edition (remote query timeout (s) option setb to 300)

Fri, 27 Nov 2009 15:51:57 Z

Hi,

I am wondring when I set 'remote query timeout' option to 300 second but when I remotely taking the session of this server from other SQL Server it still executing the query more than 5 Min.

sp_configure 'remote query timeout (s)' ,300
reconfigure with override

our aim:- any application team member or any remote user if executing the query & it taking more than 5 MIN it should to time out.
I have set remote query timeout parameter by using above command & I also cross in run value its updated.
enviroment:-SQL server 2000 Enter Edt
logshipping configure in this servers as aa secondry.

please advice.

reagrds
ravi

india

SQL Server 2008 Backup?

Tue, 18 Aug 2009 17:50:24 Z

Hello:

I have database which is size of 12GB & this database is enabled for FILESTREAM. filestream size is is 500GB+ & when i do full backup it takes more than 24hrs+.

For database restore testing, when i restore the database it takes more than 23 days which is not acceptable.

Any help to expedite backups & restore will be highly appreciated. SQL partition is not in our solution & don't want to use as that will lead us to re-design of Database & we don't want to redesign at this moment.

Thanks

Free up memory on SQL Server

Wed, 25 Nov 2009 10:58:11 Z

My SQL Server 2005 has 38Gb allocated to it and its currently using 42Gb, problem is it never frees up any of this memory for other processes and the app I have running on SQL is now becoming unusable...whats the best way to free up the memory, the system used to run on only 24Gb and started to have issue so we slowly increased the memory availbale until it was at 38Gb, each time I have increased the memory the app works fine for a day and by the next morning it has eaten up all the memory. I have 48Gb in the server but don't want to give out any more.

Can I set the memory to 24Gb again (without a restart) and go through the process of slowly increasing the memory again.

Cheers
Craig H

Database suspect -> unable to get it back online

Wed, 25 Nov 2009 10:34:07 Z

We have an SQL 2000 Server which uses SAN storage for its data. On Monday the link to the san storage was lost and when it came back online one of our databases was marked Suspect and of course the applications using the data in this dbase no longer worked.

At first I figured I'd just restore the backup from Friday night so we'd just lose the work of Monday morning and that's when I found out the last successful sql backup was on 13/09/2009 so restore is not an option.

The mdf file is approx. 8.4GB large and the ldf file is approx. 4.7GB large.

I searched the internet for possible solutions and have tried several things.

Before I did anything I copied the mdf and ldf files to a different location in case I screw up the original ones even more.

The reason given in sql server log for the suspect status is as follows :

Error at log record ID (1355717:2926:4)
Error:823, Severity:24, State:2
I/O error ( torn page) detected during read at offset 0x00000120db000 in file D:\.......\mdb.mdf
Error:3414, Severity:21, State:1

First of all I tried the sp_resetstatus command but the database stays in suspect mode.

I then put the database in emergency mode and created a new empty database with default size of 1MB to recover the data into.
Unfortunately the DTS job created ( using Copy SQL Objects Task ) locked up at 20% and SQL Enterprise Manager also locked up so I had to abort this operation.
It was only afterwards in another article that I found out your new database to which you want to recover data must be at least the same size as the original one ( yes it may seem logical to you but not to someone who does not know how sql works exactly. I figured sql would grow the database as needed ).
Our SQL 2000 Server does not seem powerful enough to create a new database to recover in of size 8.4GB and log file size 4.7GB.

At this point I figured it's acceptable is the log file is lost, as long as I get the database up again so I ran DBCC checkdb ('mdb',repair_allow_data_loss) on the database but that process also locked up.

As a last resort I found an article online that explains you can detach the database via query analyzer and then re-attach it with only the mdf file using sp_single_file_attach_db.

The detaching worked fine but when trying to re-attach the database I get error :
Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x00000120db0000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\mdb.mdf'.
Connection Broken

Basically this leaves me with no way to perform any more operations on the database.

I then took a spare server and installed SQL Server 2005 on it and I was able to get the database on that server in suspect mode.
Problem is that whatever dbcc checkdb I want to run on it, I always get the message that I need to upgrade the database before this can be done.
I don't want to upgrade the database to SQL 2005 format since it has to run on an SQL 2000 server.

What I tried next is to create a new empty database on SQL 2005 Server but in SQL 2000 format with at least the same size of the original database and log file, then de-attach it from the SQL 2005 server and then attach it to the SQL 2000 server but even that won't work. Something about incorrect sysindexes.

Next step : on the SQL Server 2000 create a new database 'mdb' with same mdf and ldf file name with default size (1mb). The server performed this operation w/o a problem.
I then stopped sql services and renamed the new mdf and ldf to *.bak extention and renamed the original ones back to extentions .mdf and .ldf and started the sql service again.
The database is now again visible in sql enterprise manager in status 'suspect'.

I probably did a lot of things wrong but there's nobody here who knows much about SQL and since it's "my application" that uses the database I have to fix it.

Any help or ideas are more than welcome.

References used : http://www.spaceprogram.com/knowledge/sqlserver_recover_emails_received.html
http://www.mssqlcity.com/Articles/Adm/attach_database.htm

SQL Server is backing itself up daily! Why?

Tue, 24 Nov 2009 20:24:15 Z

I don't understand what is going on with my SQL Server 2008 install.
I have setup a backup plan for each of my databases that includes a full, differential, and TA logs.

Today I went to restore each of my databases (including master, model, and msdb) into a test server.

After restoring the system databases I went to restore my user databases but I couldn't restore any of my differentials!

Upon investigating I checked the msdb.backupset log and noticed that for some reason the user "NT AUTHORITY\System" is doing a daily Full backup (with the is_snapshot flag set to 1) for each of my databases.

It seems to me as if my differentials use these daily Full Backups as their base which is why I can't restore any of my differentials.

I cannot tell where these backups are being saved to nor can I tell what is triggering these backups. Can anyone help?!?!

The Apprentice

sqlmaint.exe failed

Mon, 23 Nov 2009 20:49:08 Z

Hi,

We have a backup Maint Plan to backup all the databases which are in FULL recovery model. It was working fine till last week. Now we are getting job status as Failed. But we could see all the backup files are getting created successfully only job status is showing as "FAILED".

We are getting the below error in the job history show details. It's on SQL 2000.

Even I deleted Maint plan and created again, i changed job owner to "sa". But it's returning job status as Failed.

Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

Thanks.

MS SQL Error 15023

Tue, 22 Sep 2009 12:36:05 Z

I have three different environments in my database: DEV=Development, TRAIN=Training and PROD=Production, When I copy PROD onto TRAIN or DEV I do it by creating a Backup of PROD then restoring it onto TRAIN and DEV. Then I change the Login Properties User mapping. this works fine for the TRAIN environment, but for DEV when I try to check the 'master' dbo as public, I get back Error 15023. I tried droping the owner and setting again, but no luck. any suggestions?

SQL server

Tue, 24 Nov 2009 18:42:48 Z

Hi all,

Could any one please help me on below error - Differntial Backup is getting failed.
OS- Windows 2003 Enterprise Edition - 32 bit
Physical server

Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date:  24/11/2009
Time:  12:17:50 AM
User:  N/A
Computer: KNPC
Description:
SQL Server Scheduled Job 'Databases - DIFF Backup Plan' (0x6A705D374EBF7142AC99D60D89ED15DF) - Status: Failed - Invoked on: 2009-11-24 00:05:02 - Message: The job failed. The Job was invoked by Schedule 221 (Daily, Except Sunday). The last step to run was step 2 (DIFF Backup of ALL Databases).

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date:  24/11/2009
Time:  12:17:46 AM
User:  KNPC\KNWWsql
Computer: KNPC
Description:
18210 :
BackupMedium::ReportIoError: read failure on backup device 'VDI_F5047433-F004-4C1D-A6C4-F5427DC53656_1'. Operating system error 995(error not found).

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 22 47 00 00 10 00 00 00   "G......
0008: 06 00 00 00 4a 00 55 00   ....K.N.
0010: 4d 00 42 00 4f 00 00 00   P..C....
0018: 00 00 00 00               ....

Thanks,
Bala.

DBCC command to CLEAR CACHE in SQL Server 2005

Wed, 25 Nov 2009 03:23:14 Z

What is the DBCC Command to clear Cache.

What is the use of 'Auto Close' ( Database property ---> Options ---> Auto Close ).

Can we use the below DBCC commands to clear cache...

DBCC FlushProcInDB(5)

DBCC FREEPROCCACHE

Thanks

Naveen Mukkasa| Press Yes if the post is useful.

How do I run a command line SQL command in Windows?

Tue, 24 Nov 2009 10:49:18 Z

I tried to run an ISQL command but I get task or command "not recognized". I need to stop a restore that failed. I am not that familiar with WINDOWS SQL Server. I am using SQL Server Management Studio, SQL 2005.

thanks

convert drive that has active SQL database

Mon, 16 Nov 2009 00:42:39 Z

Hi Forum,

i have a kind of problem, that my database of SQL stored in FAT32 drive, if i want to convert (DOS Command) to NTFS file system, how do i do it without any impact with my existing database, because i could not stop the production.

thanks in advance,
-ito-

Repair Procedures do not allow for selection of MSSQL Instance

Fri, 20 Nov 2009 22:44:07 Z

Envronment: Windows 2003 ENT, SQL Srvr 2008 x32.

I have my OS on one channel - 2 drives, RAID 1 (Drive C:)
I have my SQL server program and files on another channel, 6 drives, RAID 5 (Drive:D)

My OS drives crapped out and I had to rebuild the OS. The SQL drives and program were untouched on the other channel. After installing W2003 and applying the usual, I then tried to Repair the SQL server instance on the D: drive. All of my SQL files and instance are on the D: drive. A copy of the SQL 2008 disk is on the D: drive and I ran setup.exe from there. The problem is that on the "Ready to Repair" screen, it did not list my instance on the D: drive, just the following: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091120_163654\ConfigurationFile.ini. The SQL disk was not in the drive.

How do repair my instance? Thanks

sql 2000 restore database error

Sun, 22 Nov 2009 14:51:36 Z

Can anyone advise on the error below am getting on trying to restore a sql 2000 database

THE MEDIA FAMILY ON DEVICE "D:\SUN-5 SQL BACKUPS\SUNDB5.BAK" IS INCORRECTLY FORMED.
SQL SERVER CANNOT PROCESS THIS MEDIA FAMILY. Restore database is terminating abnormally

why so many extens are changing in simple insert/update/delete??

Mon, 23 Nov 2009 12:39:27 Z

I am (basically a java developer) facing the prob that my live db is 7.5 GB and its diff backups per day are 1.5 GB. (in fact we take diff backups for 4 hr) When i ran randy's script (http://www.sqlskills.com/BLOGS/PAUL/post/New-script-How-much-of-the-database-has-changed-since-the-last-full-backup.aspx ) for the half day (i.e at lunch time which is half of a complete business day) I got following output:

total change percentage

125658 17169 13.66

I am sure this is an old application and very few ppl use this but how can this be possible to show 17169 changed extents which is causing: 17169 * 64k => 1098.816 MB (nearly 1GB differential) for such a less used application. To debug further I made a one row insert, one update and seen nearly 5 extents are changing. and after inserting 130 rows early 10 extents are changing. I am totally confused in understanding how db extents are changed and how can we minimize to get smaller diff backups? any links/help on this would be highly appreciated.

PS: no index rebuid/reorg happened to shuffle the data. All table's indexes are clustered (i.e primary key columns)

Operating system error 38 in SQL Server Backup

Fri, 20 Nov 2009 16:35:58 Z

We are receiving the error given below when the backup jobs is running. The thing to note here is the job completes successfully but we are getting the error logged in the log file. We use native SQL Server backup, server is running SQL Server SP3 CU1.

BackupIoRequest::WaitForIoCompletion: read failure on backup device 'n/a'. Operating system error 38(Reached the end of the file.).
Error: 18210, Severity: 16, State: 1.

I tried searching in the internet but nothing could help me with this specific error, i see a lot for OS error code 995, 1227, etc. Can anyone post some valuable comments on the error.

SugeshKumar
SQL Server MVP
http://sugeshkr.blogspot.com

Analysis DBCC CHECKDB output

Fri, 20 Nov 2009 04:44:29 Z

Hello I'am newbie in SQL server

I am using MS SQL server 2000

recently, i saw error on DBCC CEHCKDB message as below

------------------------------------------------------------

    Server: Msg 8946, Level 16, State 12, Line 2
    Table error: Allocation page (4:32408616) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page.
    Server: Msg 8921, Level 16, State 1, Line 1
    CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
    Server: Msg 8998, Level 16, State 1, Line 1
    Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 9 pages from (4:32408616) to (4:32416703). See other errors for cause.
    DBCC results for 'qhistoriandata'.
    CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
-----------omit simple info ----------------------

    DBCC results for 'sysfulltextcatalogs'.
    Server: Msg 8939, Level 16, State 98, Line 1
    Table error: Object ID 99, index ID 0, page (4:32408616). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
    There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
-----------omit simple info ----------------------

    DBCC results for 'sysfilegroups'.
    There are 2 rows in 1 pages for object 'sysfilegroups'.
    CHECKDB found 0 allocation errors and 1 consistency errors in table 'ALLOCATION' (object ID 99).
-----------omit simple info ----------------------

CHECKDB found 1 allocation errors and 1 consistency errors in database 'QHistorianData'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

---------------------------------------------------

so, i excuted DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option
but i couldn't fix this error wih "the system cannot self repair this error"

so i am going to do someting as below

1. i have backed up BAK file that has same problem of current active database. so i will make new DB . then, i want transfer validated data into it from backed up BAK file. so is it available?

2. for fixing this error, i will analysis DBCC output message, but i can't understand this message. i want what table or view has corruption. what is object ID, page id(?:????????) and so on. so let me know how to understand this message?

I appreciate you for reading this post

if you give me some tips, it will be too much help for me.

thank you

Should I backup the drives that hold the MDB and LDB files?

Sat, 21 Nov 2009 15:00:21 Z

I have 2 drives that house log files, and another 2 that house my data files. The backup job includes a SQL backup of all my databases, and also backs up the 4 drives, full on Sat and dif on the other 6 nights. Is there any reason to do the disk backups? The log and data files are the only thing on these drives.

Our system isn't OLTP, all the databases use Simple backup, and should we lose a database, I would restore it from the night before and have the users reapply their data. I'm trying to see if I can cut down the backup job time, and use less tapes.

Bruce

How to rename SQL Server virtual name

Tue, 17 Nov 2009 15:22:20 Z

I need to come up with a way to rename one of our SQL Server 2008 virtual names. The SQL Server instance is part of a two node Windows Server 2008 R2 failover cluster. According to Microsoft (http://msdn.microsoft.com/en-us/library/ms178083.aspx), the only steps required are to rename the SQL Server network name and then take that resource offline and bring it back online. I guess I'm a little confused at how the VCO comes into play here. The aforementioned article makes no mention of that. I'm assuming one needs to create a new VCO computer account and grant the CNO account full control. Should that be done prior to renaming the network name? Has anyone attempted to do this before?

thanks
matt swanson

--- Matthew Swanson Windows Server 2003 MCSE

How do you change an existing Passive Cluster into an Active cluster and at what point do I install SQL Server Enterprise?

Thu, 19 Nov 2009 19:23:15 Z

HI,

I need to install a new database (BB) onto the “new Active Cluster”.

Cluster Servers are SQLCLUSTER 1, which is currently Active, and SQLCLUSTER2, which is currently Passive. SQLCLUSTER2 is the one we what to change to Active.

Current resources are: Disk R (SQL Data), Disk S (SQL TLog), Disk T (currently name is WebCT Data) and Disk U (currently name is (WebCT TLOG). All the above resources are on SQLCLUSTER 1. We need to take Disk T and change the current name to (BB9 SQL Data) and U to (BB9 SQL TLog).

Drive T and U need to be moved to the new Active cluster, SQLCLUSTER2.

Also, when changing an existing Passive cluster into an Active cluster, do the end users see any difference after the change?

Thanks, CaptSky

Sql Server 2005 ( Copy job disabling automatically )

Tue, 23 Jun 2009 12:12:22 Z

Dear All,

In my enviroment i have logshipping between three servers .... from some times we are facing the problem with copy job ( which configure in secondry server ) ...it has disable automatically .....i don't know why this happening ?

please help

regards
ravendra

india

SQLVDI: Loc=CVDS::CreateEx. Desc=Security initialization failed. ErrorCode=(0). Process=2936. Thread=5296

Fri, 20 Nov 2009 13:26:26 Z

We got some error messages in SQL SERVER 2005 in MS 2008 server.
When we restored SQL server by Symantec production, We got the messages.

If you have the solutions for the errors, please provide us with your help .

thank you from Japan.

(error logs)
SQLVDI: Loc=CVDS::CreateEx. Desc=Security initialization failed. ErrorCode=(0). Process=2936. Thread=5296. Client. Instance=. VD=Global\msdb_

SQLVDI: Loc=ShareProcessHandle. Desc=GetSecurityInfo. ErrorCode=(5)アクセスが拒否されました。
. Process=2936. Thread=5296. Client. Instance=. VD=.

Active/Active Cluster and Storage

Mon, 16 Nov 2009 20:23:24 Z

Hi,

my english is not so good... but lets try to make me understood...

I have 2 servers running the same IIS app and a Storage, what i want is put the databases in the Storage and access from both servers as active/active... but with failover tecnology... i have readed a lot of docs... but i already have some questions...

PS: I already have the QUORUM and MSDTC disks...

1 - With 2 servers as in cluster, its necessary a third server for "monitoring" the others ?
2 - Failover cluster configurations can be used for active/active cluster ?
3 - I have readed that its necessary to have a third server to be a domain controler for the other 2 servers... is that right ?
4 - I have readed too that active/active its not recomended because it can cause a virtual memory fragmentation.. is that right ?

can anyone giveme an idea of what is the best practice for this scenario ?

another question that is... if i have one database for each client, if i can redirect all the requests of one client for the same server i dont need of cluster, right ?

Thanks

Luiz

SQL High Availablity Options

Thu, 29 Oct 2009 20:02:34 Z

We currently have the SQL 2005 on Win2k3 setup with the MSCS and SQL clustering within a datacenter.
And between the active-passive datacenter we are using the log shipping to replicate the data.
However, I would like to know if there's any available/proven options create a better HA environment (for both inter and intra). One of our major concern is that when we apply the SQL patches/hotfixes, there is a risk of brining down the entire cluster. Also, whenever we failover to the passive datacenter it takes about 20mins outage.:

SQL Clustering - with SQL 2008 on Win2k8 what kind of advantages you are getting over SQL 2005/Win2k3 (or even SQL 2008 and Win2k3)? - for example, when you apply the SLQ patches in a cluster environment, doe is create a lesser down time?
SQL Replication - can any SQL replications such as peer-to-peer replication or mirroring improve the situation?
3rd party replication/clustering - can any non-MS solutions help us to create the SQL HA environment (I have used the Veritas Cluster for my previous projects and had some advantages)

Thank you.

SQL 2008 cluster configuration - disk

Sat, 14 Nov 2009 16:32:26 Z

Hello,

I have to install a SQL 2008 cluster, right now we have an md3000i with 6 146GB disks...whats the best layout for this cluster? I mean how should I divide it to make the Quorum, the SQL data and the Log disk...what raid should I use? etc..

Thanks!

Log Shipping Script Configuration Doesn't Generate Complete Script

Wed, 18 Nov 2009 11:27:58 Z

Hi,

I have a really simple log shipping configuration running on a single SQL 2005 primary to a single SQL 2005 secondary for 3 databases.

I wanted to script the configuration so I could re-initialise it easily (e.g. in the case of a broken chain), and the script configuration option in sql management studio worked fine for two of the databases, but gave me a slightly wrong script for the third!

It got the primary and monitor bits correct, but for the secondary, it missed the destination directory:-

,@backup_destination_directory = N''

And it also created the jobs with the default schedule (every 15 mins every day etc).

I looked at the configuration screens again to make to the settings weren't gone, but they were still there present and correct. Every time I ask it to script the configuration though for this database i get the same results!

Is this a known issue? I am hoping I haven't done anything wrong considering the other two databases script out absolutely fine.

Cheers
Dan

SQL 2000 backup unable to see local drives

Wed, 18 Nov 2009 09:06:22 Z

I have a SQL 2000 cluster running on Windows 2003 Enterprise server, the cluster shared storage is provided by a Dell SCSI disk array.
Im trying to backup the databases to an ISCSI drive on our SAN. When I select the backup locations for the databases in the maintenance plan the server only shows 2 drives (R & S) which are on the Dell expansion box, all other drives are not visible (local drives c: and d:, quorum drive q: - also on the Dell disk array and finaly x: ISCSI drive on our SAN)

Can anybody tell me why these drives are not available to select as backup locations and how to add them.

Thanks in advance

Transcational Log for MODEL db ?

Tue, 17 Nov 2009 03:49:40 Z

Is it required to take transcational log backup for 'Model' DB? As we are facing a problem as model db disk space is full but unlikely there is lot of space in the disk.

Error code is 112

Naveen [MCTS] | Press Yes if the post is useful.

Logshipping from SQL Server 2008 to SQL Server 2005

Tue, 17 Nov 2009 14:13:16 Z

Is it possible to do log shipping from sql server 2008 to sql server 2005

SQL Server 2000 Enterprise Edition ( Backup time suddenly increased )

Tue, 10 Nov 2009 14:37:29 Z

Dear All,

In our production enviroment we have configured full backup job and its taking maximum 3 hours..
but now it taking more than 6 hours for completing backjob.

same db we have configured logshipping also but this problem never come ..why suddenly increase this time.

nothing we have changed at in DB

reagrds
ravi

india

Move or Restore a Filegroup to a different database

Thu, 29 Jan 2009 23:34:35 Z

Hi There,

This is regarding moving or restoring SQL Server 2005 FileGroup to a different new Database on the same SQL server.

Requirement: Our database size is 2.5 TB and 1 TB of it is a single table, LogTable. This table is in a single file group, FGLogTable. Ofcourse the backup size is getting longer and longer, spanning to working hours. What we are thinking is to move this table/File group to a different database. Hope this will help us to reduce the backup time a lot.

Options: Create a secondary Empty database, with the same FileGroups. Detach/Copy the FGLogTable and Attach/Restore to the Secondary database. I know when we retore the FileGroup it is going to check for the GUID of the files and file group. Is there any work around so that we can Restore this FGLogTable filegroup to a different database? We really do not want to INSERT-TRUNCATE route, because the number of records in the table is 2 BN.

Thanks,
Sams

Sams

SQL Server 2008 GEO/Multisite Clustering

Mon, 22 Sep 2008 16:10:02 Z

I would like to know when and if SQL Sever 2008 will support multisite clustering and how to achive this. I have already installed/tested the basic multisite clustering for static data but would like a more detailed document on the procedures. everything so far have been general high level with papers indicating it can be done but give no real direction on the configuration.

Rename Cluster Name

Mon, 16 Nov 2009 19:17:51 Z

Hi,

As you know, in a clustered setup, there is the Server Cluster Name & IP and there is the SQL Server virtual Name and IP.

if I intend to rename the Server Cluster Name, do i need to worry about anything that is related to my sql server 2005 ?

Tarek Majdalani | MS Forefront Edge Security MVP | http://www.elmajdal.net

backup job in sql server job fails

Sat, 14 Nov 2009 19:12:37 Z

I'm using a maintenance plan package in a SQL Server job.

- When I run the maintenance plan separately, the physical backup is created
- when running it from the SQL Server job, no file is being created, but no error occurs
- when running the SQL Server job scheduled it works

What could it be?
I would think, when running it manually, my security credits will be applied. And I have access to the folder

Business Intelligence professional

SQL Server 2005 (Database mail configuration operator property)

Fri, 13 Nov 2009 14:10:33 Z

Dear All,

We have configured the Database mail in One Server & its owrking fine ..now I want to configure Database mail in other server but I forgot the Server name (asking when we are creating account ).
can I check it from previous Database mail configuration property what server IP we have given ..

how to check SMTP is enable in Server by command (If it possibe please provide SQL Command)

Regards
Ravi

india

Moving SQLServer 2008 Cluster to a new domain

Thu, 12 Nov 2009 19:02:02 Z

I have a two node SQLServer 2008 cluster on Windows 2008 Server. The cluster needs to be moved to another domain. Is this possible without having to uninstall SQL Server and destroying the cluster? Or will we need to uninstall SQL Server, undo the cluster, and start all over?

SQL 2008 Active Active cluster

Thu, 12 Nov 2009 10:38:40 Z

Hi, we currently run an active passive sql 2008 configuration as follows:

Cluster 1 instance 1
Node 1                                       Node 2
C: System                                 C: System
D: SQL Binaries                          D: SQL Binaries

              SHared Disk F SQL DATA & DB TEMP
              SHared Disk E SQL LOGS
              SHared Disk Q Quorum
              SHared Disk M DTS
              Virtual Network Name
              Virtual IP

Now, im thinking that to make active active, i need top add a new set of shared disks between the two nodes,

so i will have something like this.

Cluster 1 instance 1
Node 1                                       Node 2
C: System                                 C: System
D: SQL Binaries                          D: SQL Binaries

              SHared Disk F SQL DATA & DB TEMP
              SHared Disk E SQL LOGS
              SHared Disk Q Quorum
              SHared Disk M DTS
              Virtual Network Name
              Virtual IP

Cluster 2 instance 2
Node 1                                       Node 2
C: System                                 C: System
D: SQL Binaries                          D: SQL Binaries

              SHared Disk G SQL DATA & DB TEMP
              SHared Disk H SQL LOGS
              SHared Disk T Quorum
              SHared Disk S DTS
              Virtual Network Name Instance 2
              Virtual IP Instance 2

is this correct,
many thanks in advance

What is happening when I restore a SQL Server 2000 database?

Thu, 12 Nov 2009 09:45:25 Z

Hi,

I had to make a restore on an old sql server 2000 database yesterday at a client. He complained that Enterpise Manager hangs when he click All Tasks-Restore for a certain database. When I looked at it, I noticed that it doesn't hangs, it just takes a whole lot of time.

I set up a trace on it and I noticed that it makes almost 2500 querys to the msdb database, asking for a backupdevice with a backup_set_ID - one for every backup taken from the creation of the database.

select physical_device_name,device_type from msdb..backupmediafamily
where media_set_id =
(select media_set_id from msdb..backupset where backup_set_id = XXXX)

The "physical_device_name" is a path to a backup file that, most of the time, does not longer exist.

In the restore window, the .BAK-file from the last full back up, and the transaction log backups after, is shown. I don't get why it has to make a query for every back up ever taken, when it has no use of it.

Can someone explain that for me?

System Database backup

Thu, 12 Nov 2009 04:42:25 Z

In my organization we follwed the full backup schedule...
Every evening full backup has been taken..
But we didnt backup the system database and transaction log
How frequently backup the system database and transaction log

How to automate the backup of transaction log
plz suggest me the schedule of backup

SQL 2000 logshipping from 3 server to 1 server, use target as monitor server

Wed, 11 Nov 2009 21:49:15 Z

Hi:

1.
Could you help me to figure out if it is OK to have the following log shipping setup. All servers are SQL 2000. SQL server A, B, and C will be set up to logship to server D. I plan to user server D as the log shipping monitor for all log shipping set up among all servers.

2.
May I use a share on SQL server A as the backup disk directory for server A, B, and C?
3.
On server C, I will use one shared destination folder for the transaction log copied over from SQL server A, B, and C.