InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 17 May 2008 08:59:50 GMT20http://www.sqlservercentral.com/Forums/Topic502490-357-1.aspxHello, can any one tell difference between format, noformat, init, noinitSat, 17 May 2008 04:00:18 GMTsathishmssqlhttp://www.sqlservercentral.com/Forums/Topic501774-357-1.aspxSo I am having a discussion with some collegues at work regarding a standard sizing for backup space. We backup to a separate backup disk on the server, and sweep through and pick up the backup to VTL. I have been telling them that I recommend 2.5 times the potential growth, which is essentially the size of the data disk. (of course it would never get past 90% as we would always expand the disk)I stated, on a 100GB data disk, we should provide a 250GB backup disk. They think I am nuts. 1.5 times is enough (150GB), they say. My problem with this is even if you are only doing a weekly full and retaining one week, once your backup gets to 75.1GB... you cannot create your new backup and then delete the older backup. Which I think is the safest way to go... you should have a validated backup before you remove the old one. And that is only a minimal full backup with 1 week retention. We intend to do weekly fulls and daily differentials.I think the problem is that we have customers that make lofty requests like, "We want a 500GB data disk." And then they throw a 10GB database on it that grows a gig a year. So why make that customer buy 1.25TB of backup space, right? My point would be, why give them a 500GB data disk. How about a 30GB data disk and 2.5 times that for their backup disk, and grow as needed. It saves on cost, and they will always have space for minimal backup requirements.I was curious what some of you thought about disk sizing for backups. I know it can vary drastically based on retention.Thu, 15 May 2008 23:34:48 GMTSean Kohlerhttp://www.sqlservercentral.com/Forums/Topic502166-357-1.aspxHi, I am facing a situation where in I have a Test Server and a development server. Right now end users are doing a UAT on the test server and they report the issue using our Issue tracking system. What I want is to have a process that will automate the back up from the Test Server and restore it on the Dev Server. Both these servers are 2005 Standard Editions ThanksVinojFri, 16 May 2008 09:18:27 GMTvinojsasidharanhttp://www.sqlservercentral.com/Forums/Topic500825-357-1.aspxLet's say I take a Full backup 7 days a week at midnight, and am also running Transaction log backups 24/7.Now, on Monday, I need to restore back to Sunday at 6:00 pm, but my Friday & Saturday full backups got overwritten. Can I restore to Sunday 6 pm by using the Thursday midnight backup, and all the t-logs since then, even though there were full backups taken in between ? The Fri & Sat full backups don't break the t-log chain, do they ??Wed, 14 May 2008 12:41:07 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic501749-357-1.aspxDoes anyone has such experiences? If yes, please describe it in detail.We have backup of master using LiteSpeed. Now, we would like to restore it.Any input will be greatly appreciated.Thu, 15 May 2008 21:08:05 GMTSQL ORACLEhttp://www.sqlservercentral.com/Forums/Topic499723-357-1.aspxExample:[i]Let's say I have a 500GB database. A full backup takes 5hrs to complete. I start the full backup at 12AM.I run transaction log backups every 20 minutes. These take 1min to run. The full backup runs between 12AM and 5AM. Transaction log backups run at 12AM, 12:20AM, 12:40AM, 1AM and so on. There will be 15 transaction log backups taken in the same period of time the full backup is being taken.[/i]So my questions:Does SQL Server only backup the state of the DB at the time the full backup starts or does the full backup reflect changes made during the time the backup is being made? If the full backup reflects changes, where does the full backup end and transaction log backups begin? Do transaction logs hold duplicate data somehow?I understand that transaction log files and data files are completely different things. But I'm confused on how the different backups handle changes to the db.Thanks,RobTue, 13 May 2008 09:18:45 GMTRob Symondshttp://www.sqlservercentral.com/Forums/Topic500930-357-1.aspxAs the Sr. SQL Server DBA I'm being told by our Sr. Network Admin that I need to consolidate all of my Maintenance plans to a central backup server. Basically removing all of the backup maintenance plans that I have created on each database server and re-create them as SSIS packages that sit on and run from this 1 centralized server.Am I being stubborn or thick headed? This just doesn’t seem like best practice to me. His entire argument revolves around making it easier to manage so he doesn’t have to know 16 Different SQL Servers and only has to deal with one physical server in the event of my absence. I've tried to reason with him but he just spouts stuff about me giving up job security and making it easier for others. I think he’s just too lazy to open SSMS and register the different SQL Servers that he would need to connect to. It's not like he doesn’t have sufficient permissions.Wed, 14 May 2008 15:26:42 GMTAndrew Pfeifferhttp://www.sqlservercentral.com/Forums/Topic498628-357-1.aspxI have recently changed my database from simple recovery to Full recovery. I have created a new backup plan which backups the full database at night 4 differential backups and a transaction backup every 20 minutes throughout the day. However my initial problem was that my backup device file was getting too large which I found to be the transaction logs. One transaction log in the device was 2GB, if this happened every 20 minutes I would have run out of space so I turned it off.Now my ldf file was not being truncated and that grew too large for the disk resulting in down time. I managed to shrink the ldf file and set the file to a restricted size. However this then became full again resulting in down time.My question is how can I configure the database to full recovery whilst keeping file sizes down?Point to note is that this is on 3 databases that join to work as one in a very large system. All have similar issues but with different sizes.Mon, 12 May 2008 03:52:28 GMTDarryl Sibsonhttp://www.sqlservercentral.com/Forums/Topic501786-357-1.aspxIs any one know the method of viewing .ldf file (Log File) . I have a database . I want to see all the DML command given to that database .Regards,REJI P RFri, 16 May 2008 00:13:09 GMTrejiphttp://www.sqlservercentral.com/Forums/Topic501449-357-1.aspxHow much time would it take to Restore a Database from Full Back fromRemote Location to a server on different location.DB Size - 100 GB Approx.Back UP File size- 20 GB Give me some good Tips for this process to make this Job faster and Easier.Thu, 15 May 2008 09:31:55 GMTaspirant.dbahttp://www.sqlservercentral.com/Forums/Topic500313-357-1.aspxHelloI have a client who is running out of disk space rapidly. Every day he goes in and runs a backup of the transaction logs using query analyzer and then right clicks on the database to shrink the database. He also has a maintenance plan in place to backup the database's daily and keep 3 days worth of backups. I have altered this to only keep two days worth of backups and also created a new maintenance plan to backup the logs every morning so he does not have to. My question is,.... Is there a way of automating the shrinking of the database? If so can someone point me in the correct direction. We are using SQL 2000Thanks GarethWed, 14 May 2008 04:57:51 GMTgrc1ukhttp://www.sqlservercentral.com/Forums/Topic501011-357-1.aspxi had restore one database "AUTO".I processing i had just stopped it.and again i want to restore same database but it showing "AUTO (Loading)" and i am not able to restore it againWed, 14 May 2008 22:49:37 GMTDilip7597http://www.sqlservercentral.com/Forums/Topic500823-357-1.aspxHi all, I am trying to create a script named script_H_v.1.0 that generates all objects in the database named Reporting_H .I have done this before many of times in the past without any problems. I do the usual steps in script wizard that it walks me through, save the script to a file, and let script wizard create the file. For some reason There is an error saying'an item with the same key has already been added'it fails on the very first action 'Determining objects in the database Reporting_H' that will be scriptedNone of the actions are successful. The script is not created. I have tried renaming the file, storing the file in a different location, restarting the comp. Here is the part that baffels me the most, Other users do not have the same problem as I do. They had success in generating script. So apparently I am doing something wrong. Please help thanks-Chris Wed, 14 May 2008 12:40:35 GMTctics112http://www.sqlservercentral.com/Forums/Topic500918-357-1.aspx:crying:Initially I setup Principal and Mirror. They are running fine. Now at witness after creating Master Key and Certificate for Witness, user andlogins of Principal and Mirror .I created certificate for IP88_usr it was created smoothly but for Mirror Login (ip999_usr) its rais the error.Note : At creation of time mirroring was up running. For Witness server, when ran following command for IP888_user is works fine, but for IP999_user I am getting error message (IP88_user is from Principal and IP999 is from Mirror) USE master;CREATE CERTIFICATE 300_certAUTHORIZATION IP999_userFROM FILE = 'C:\Certificate\300_cert.cer'GO Msg 15232, Level 16, State 1, Line 2A certificate with name '300_cert' already exists or this certificate already has been added to the database. When I search sys catalog there is no certificate of this name, and when I try to drop it gives error do not find this certificateI recreated every thing after droping certificate,login user endpoint but still getting same error.Wed, 14 May 2008 15:02:35 GMTSIDhttp://www.sqlservercentral.com/Forums/Topic499564-357-1.aspxHi,Could someone has any script to suit following requirementNeed to create Store Procedure which will first look into sys.dm_db_index_physical_stats view and cretae script for1) REORGANIZE when avg_fragmentation_in_percent value <30%2) REBUILD when avg_fragmentation_in_percent value >30%3) Incase if index is clustered in 2'nd case then create REBUILD TSQL Command for all noncluster index in that table.It should not automatically run but just need to create a Script with these TSQL Command.Thanks in advanceTue, 13 May 2008 07:15:38 GMTnkm129http://www.sqlservercentral.com/Forums/Topic500272-357-1.aspxHi,I added a full backup task to the maintenance plan of our new SQL Server.It's setup to do all user databases and to create a backup file and a separate subdirectory for each database.This is just the way i want it except for the part that each filename has the datetime stamp. Is there some way to disable this? So i only have .Bak? The reason i need this is because we upload these backups with a online backup utility but there is no option there to remove certain files.Thanks!!Wed, 14 May 2008 03:40:22 GMTMarco van Kimmenadehttp://www.sqlservercentral.com/Forums/Topic500547-357-1.aspxi have a situation where i need to provide a backup plan for 64 databases all of which are running in full recovery mode.My questions is:1. should i create a backup script scheduled to execute all full backups of the 64 db's at once or should i schedule full db backups of these database to occur one after the other? thanks for yuor input guysWed, 14 May 2008 08:23:17 GMTDenbyhttp://www.sqlservercentral.com/Forums/Topic499873-357-1.aspxPlease let me know in which table the Backup retention period information is stored in Msdb/Master DB? SQL Server 2000 Edition.Thanks in advance,ManuTue, 13 May 2008 11:50:00 GMTMANUhttp://www.sqlservercentral.com/Forums/Topic499362-357-1.aspxMy hard disk is running low on free space I want to move my replicated database from this Disk to a new hard disk without losing the replication settings is it possible? or do i have to start the replication process all over again?Tue, 13 May 2008 01:26:34 GMTpmuneshhttp://www.sqlservercentral.com/Forums/Topic495398-357-1.aspxAccording to "Sql Server 2005 Books Online", Transaction log will be truncated in two situations below:1.Database is in Simple Recovery Mode && each Checkpoint2.Back up logBut the problem is:My database is in Full Recovery Mode,and i did not do any log backup.When i do a lot of trans , i can see that log file's size grows up.But the log file's unusage grows up too.It seems that transaction log truncation happened!I need to know why!Mon, 05 May 2008 23:34:49 GMTeric.zhouhttp://www.sqlservercentral.com/Forums/Topic500443-357-1.aspxHi,As per msdn help "when the AUTO_CREATE_STATISTICS database option is set to ON (default), the Database Engine automatically creates statistics for columns without indexes that are used in a predicate."Could someone explain this with example and how these statistics work with index and column which is not index?ThanksWed, 14 May 2008 07:21:26 GMTnkm129http://www.sqlservercentral.com/Forums/Topic499547-357-1.aspxHi there, I'm not exactly sure where to post this question, so I'll post it here.I have 2 Windows XP machines, not part of a network domain. One of the XP machines is running SQL Server 2005 Express edition, lets call this DB machine. The other machine is just running my application - App machine. As part of my application, I want to be able to do a backup of the database.The DB machine is also running the application. If I log into my application on the DB machine using SQL Server Authentication, and run the backup it works fine. (It's using the T-SQL BACKUP command). If it log into my application on the App machine, and try to do the backup, I'm getting an error saying that "The user is not associated with a trusted SQL connection". The same user is being used in both scenarios, and this user can update the database fine on the App machine, so it's not really a connection problem, it seems a permission problem. The SQL user I have created is a member of the db_backupoperator role for the required database.Is anyone aware as to why I would be getting this error?Tue, 13 May 2008 07:02:32 GMTnealhudsonhttp://www.sqlservercentral.com/Forums/Topic490360-357-1.aspxHello all,I am having a strange problem with a Transaction log backup which runs on a 2005 database every two hours. Due to a high volume of change in the database the TLog grows at very fast rate and will fill a its disk in no time. I created a maintence job which backs up the TLog every 2 hours. It is also supposed to delete any file older than a day, which does not seem to work either. The backup runs every two hours for a few days then stops with the following error:Executing the query "BACKUP DATABASE [SSP_Search_DB] TO DISK = N'F:\\Backup\\Special_TLog_Backup\\SSP_Search_DB\\SSP_Search_DB_backup_200804240600.bak' WITH NOFORMAT, NOINIT, NAME = N'SSP_Search_DB_backup_20080424060001', SKIP, REWIND, NOUNLOAD, STATS = 10" failed with the following error: "Could not clear 'DIFFERENTIAL' bitmap in database 'SSP_Search_DB' because of error 9002. As a result, the differential or bulk-logged bitmap overstates the amount of change that will occur with the next differential or log backup. This discrepancy might slow down later differential or log backup operations and cause the backup sets to be larger than necessary. Typically, the cause of this error is insufficient resources. Investigate the failure and resolve the cause. If the error occurred on a data backup, consider taking a data backup to create a new base for future differential backups. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Not sure what they mean by insufficient resources as the destination drive has plenty of space for the backups. Naturally when the TLog backup stops the TLog grows until the disk is full. Also, the cleanup job does not seem to be working. Has anyone run into this problem before? Any help you can give will be greatly appreciated. Thanks in advance.KenThu, 24 Apr 2008 17:16:16 GMTkwitzellhttp://www.sqlservercentral.com/Forums/Topic499944-357-1.aspxHi all,How can i find out what is the size of backup file that baked up everyday but usind T-SQL?ThanksPAtTue, 13 May 2008 13:45:13 GMTpathttp://www.sqlservercentral.com/Forums/Topic499567-357-1.aspxHi all,How can i restart the job when i see the error in error log about backup failed!! Also i am trying to script out so if i see failed backup error during the time job runs i need to restart the job and get back up finish with out error!!Please help!!ThanksPatTue, 13 May 2008 07:18:03 GMTpathttp://www.sqlservercentral.com/Forums/Topic499538-357-1.aspxI have some developers using sqlXML 3.0 and wonder what this is and why not MSxml, and what version supposed to use with SQL 2005 and why it won't work directly with IIS 6.0, have to use the sqlXML (IIS like interface to map the virtual directory for query sql 2005 databases).Any directions or links or website to read more to understand, really appreicate.Tue, 13 May 2008 06:57:53 GMTcheunghhttp://www.sqlservercentral.com/Forums/Topic499366-357-1.aspxIn Sql 2000 there is the msmdarch.exe utility. We can use an Windows Administrator ID to execute msmdarch to backup Analysis database. But when these administrator rights were removed, msmdarch cannot backup the database.Tue, 13 May 2008 01:35:48 GMTjimmytsuihttp://www.sqlservercentral.com/Forums/Topic499059-357-1.aspxHi all unable to restore database I have an test db, which needs to be restored with the latest backup, the problem is hard disk space, it was 43 gb free, but the latest bak file is 54gb, so in order to restore it i though of deleting the db and copying the latest bak file on it and restoring it. Now the backup is there, and 58 Gb is free in the hard disk, But the prob is when im restoring is says TITLE: Microsoft SQL Server Management Studio------------------------------Restore failed for Server 'DE1SMSGPDB01'. (Microsoft.SqlServer.Smo)ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'D:\' to create the database. The database requires 76416024576 additional free bytes, while only 63008391168 bytes are available. (Microsoft.SqlServer.Smo) i dont know whats the problem, i have 58 gb free space and 54 bak file then why its not restoring. Pls let me know asap.thanksMon, 12 May 2008 13:09:57 GMTNicolehttp://www.sqlservercentral.com/Forums/Topic499197-357-1.aspxHi,How can i trace or check error log for error related to backup of database.I need to restart the process of DB backup again fi there is failure error for back up for that day!Please Help!!Using SQL server 2005 on OS 2003ThanksPatMon, 12 May 2008 16:26:15 GMTpathttp://www.sqlservercentral.com/Forums/Topic499023-357-1.aspxA consultant in our company installed an SQL 2005 with case-sensitive collation.Because of messy system here, I found it when it was passed over to me. Now, I would like to change the collation to case-insensitive. However, all system databases are full of "components", such as logins, stored procedures, packages, assemblies, ....What will you suggest me to do for this "recovery"?Many thanks for any input in advance.Mon, 12 May 2008 12:25:49 GMTSQL ORACLEhttp://www.sqlservercentral.com/Forums/Topic491800-357-1.aspxHi all,I'm totally confused by now, reading through a few books and online material to try to figure out what the following terms mean. Sometimes it seems to me that the definitions from different sources contradict each other. I've given up on that approach and I rely on you to help me understand please. Please tell me in simple terms what the following are:Backup SetBackup DeviceMedia Set Media FamilyThank you so much!Mon, 28 Apr 2008 19:55:41 GMTshahgolshttp://www.sqlservercentral.com/Forums/Topic498575-357-1.aspxwhat is check point?what can i do if check pont expires?Mon, 12 May 2008 01:02:05 GMTreddy.leelasoujihttp://www.sqlservercentral.com/Forums/Topic498032-357-1.aspxI am looking for a script that would:1) identify all Dbs in full mode and tell me if they all been backed up last night (or when they were backed up last)2) For all dbs that are not in simple mode check if trn backups run in last hour.Can anyone help please?We have separate maintenance plans for Dbs in simple mode and Dbs in full mode, so when a new db is added to the server we might forget to add it to the appropriate maintenance plan. So that is why i would like to have this script.thank youFri, 09 May 2008 09:52:08 GMTGolovahttp://www.sqlservercentral.com/Forums/Topic497649-357-1.aspxEvery morning I get this message regarding one of our production DBs:The transaction log for database 'ABC' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.This is SQL 2005. The recovery model is FULL. Full DB backups occur every night at 01:00. Transactional log backups occur every 10 minutes. Auto_shrink is set to false and max file size is Unlimited.I don't understand why the log would become full as transactional log backups happen every 10 minutes - which of course then truncates the log, correct?It is also not a case of running out of disk space, as the volume has over 70GB of free space, (the DB data file is 17 GB and the ldf file is about 2GB when I get this error).Is there any one who can help with this problem?Thanks,M Fri, 09 May 2008 01:57:01 GMTmarissavhttp://www.sqlservercentral.com/Forums/Topic498548-357-1.aspxi have a db of 70gb.ihave 2 backup devices of 40 gb each.It is possible to take a full back up of my db using these devices.Sun, 11 May 2008 23:00:14 GMTch.mohinihttp://www.sqlservercentral.com/Forums/Topic488011-357-1.aspxWe have to push .bak files from our SAN server to a warm server. Log Shipping, Mirroring and Replication are not an option due to the antiquity of the databases (legacy ERP system)We are using the command line winzip but it is taking much longer to zip and transfer. Is there any file compression software available that is better and large files?Mon, 21 Apr 2008 08:39:35 GMTjsheldonhttp://www.sqlservercentral.com/Forums/Topic497823-357-1.aspxlets say i have a DB with 3 fileGroups, each with 3 files.Filegroup a, b and c.there is an issue with the data in filegroupc, datafile1, however the issue is not discovered for many hours. when it is discovered, the db admin decides to restore just the problematic file.the only backup he has for the file is 10 hours old.my question is , even though the database continued to function after an issue occroured, can you restore an old file into a database that has been active for many hours after the backup was taken ?Fri, 09 May 2008 06:49:53 GMTwinston Smithhttp://www.sqlservercentral.com/Forums/Topic496928-357-1.aspxI have recently changed our production database from simple recovery to full recovery and devised a backup plan so I can restore to a point in time.However I have just found that the log file .ldf has grown to 30GB and has filled up the disk it resides on. Is there any way to reduce the size of this. I have already tried to shrink and to limit the size of teh file but I cannot reduce the file below the 30GB.Help!Thu, 08 May 2008 02:06:25 GMTDarryl Sibsonhttp://www.sqlservercentral.com/Forums/Topic497793-357-1.aspxMy Maintenance Plans are failing on SQL 2005.I have tried having the SQL Server agent logged on as Administrator of the Domain and as Local System Account. The error I get is Unable to start exectution of step 1(reason line(1):Syntax Error, The step failed. The directory where the backups go is Shared. Administrator has full rights. If I look at the T-SQL window the first line begins with Execute master.dbo.xp_create_subdir N'C:\Documents\DSD'GO....I can execute manually with all the same parameters and it runs perfectly. Any help would be greatly appreciated.Thanks,DebbieFri, 09 May 2008 06:16:19 GMTdebbiehttp://www.sqlservercentral.com/Forums/Topic497636-357-1.aspxHi,As started new DBA job I found out that as DBA we need to maintain database so could someone please help me to find out solution of following issue:- How to check database integrity and what is command for update statics , shrink database and when we need to check database integrity, update statics and shrink database.Thanks!Fri, 09 May 2008 01:37:36 GMTnkm129