Maintenance Commands Affect On Log File / Log File Maintenance Without Log File Backups?
Jun 18, 2015
I am testing some maintenance tasks sql commands such as index rebuild, index reorg, update statistics and db integrity check on a SQL Server 2014 Database. This is a new non-production vendor database (DB Size 500 GBs, Log Size 25 GBs) which eventually will be created in production. Currently, it is in full recovery model and without log backups. The database has a whole lot of indexes. I am just trying to rebuild and reorganize all the indexes (that need it), in addition to trying to get an idea of how long these maintenance task will take and the space needed in the log file to complete these tasks/commands. I would like to execute these tasks manually (the first time) to gather the duration and space required information. Eventually, I would probably schedule a weekly job to perform this maintenance.
I ran the index rebuild task on the database and noticed that the log file grew by over 50 GBs. I killed the process and truncated and shrunk the log file back down.
1. Does the index rebuild, index reorg, update statistics and db integrity check commands all use the log file?
2. Does Indexs Reorg have less impact on log file then Index Rebuild?
3. Should a truncate log and shrink log file be performed after these maintenance commands?
4. Should a full database backup be performed after these maintenance commands? Or before the maintenance commands?
I have read and understand that shrinking is not good for the database (could lead to more fragmentation and more data file growth when data is added) and I know about rebuilding indexes when fragmentation is GT 30% and reorganizing indexes when fragmentation is GT 5% and LE 30%.
Since this is a non-production database maybe I should set the recovery model to simple, run the maintenance commands and leave the database in simple recovery model unless the vendor needs it in full recovery model for some unknown reason.
5. With the simple recovery model the log file should be reused in a circular manner and not grow during these maintenance tasks. Is this correct?
View 3 Replies
ADVERTISEMENT
May 31, 2006
Hi,
I'm using SQL2K with SP4 2187. I have created a DB Maintenance wizard where the purging older than 1 day is set.
However, this feature seems not to be working, even if I tried two ways. Delete the scheduled job and recreate it - not successful, 2nd) delete the Maintenance Plan, still not successful.
Is this a bug or do I miss something here.
View 4 Replies
View Related
May 9, 2000
I am trying to restore a file that was created using Database Maintanence Wizard on a server that is now totally crashed.
I want to use T-SQL to restore the physical file to a new name and location.
Using the backup command, I get the error that I must use the with move option??
What are the required steps to restore from a
physical file (with a bak extention)???
I do not know the original database/log file names. Any advice?
Thanks
Ziggy
View 1 Replies
View Related
Oct 5, 2006
What is the method to execute backups from batch (.bat) files on the server running SQL Server. I have tried the sqlmaint command - doesn't seem to execute, looked into the xp_sqlmaint with no luck. I'm sure the problem lies in my lack of DOS batch programming skills. If anyone has an example of a batch file that executes a backup would you mind sharing. thanks
View 6 Replies
View Related
Jan 16, 2008
I have the following issue with Maintenance plan backups that work for BAK DIF and TRN to a remote server share.
When I try and remove the old files with a clean up task I get an error and the files don't get deleted.
The version is as follows
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on
Windows NT 5.2 (Build 3790: Service Pack 2)
The error result is as follows,
Failed-1073548784) Executing the query "EXECUTE master.dbo.xp_delete_file
0,N'\\EXECUTE master.dbo.xp_delete_file 0,N'\ABCD-A1\BACKUPS\ABCD_BACKUP\ABC_DAILY\ABCD',N'trn',N'2008-01-13T12:52:49'" failed with the following error: "xp_delete_file() returned error 2, 'The
system cannot find the file specified.'". Possible failure reasons: Problems
with the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.
The maintenance plan seems to be adding extra "" though when i enter the
code directly in a query i get same error.
Query:
EXECUTE master.dbo.xp_delete_file 0,N'\ABCD-A1BACKUPSABCD_BACKUPABC_DAILYABCD',N'trn',N'2008-01-13T12:52:49'
Error:
xp_delete_file() returned error 2, 'The system cannot find the file specified.'
The servers belong to the same domain and are using the same Service account which has all the necessary rights to the share and the file directory location. The backups work but i get the error on the cleanup task.
Trying to figure out how to get the Cleanup task to delete old files. The same happens for all file extensions and I have tried other locations with simpler file paths same error.
Regards,
Scott
View 6 Replies
View Related
Nov 6, 2015
I need to copy a just-created bak file to another drive after the backup task has completed. I don't see anything in the job toolbox which works with file system operations like this. But still it must be a common need..There are ways to script this or use third part tools but I am looking for something native to the sql server 2012 SSMS toolset, if possible.
An alternate approach would be to run the backup job again, after the main backup, and change the destination to the alternate location. But I was thinking that another backup job would probably invoke more overhead on the server than a simple file copy operation. If I do end up taking this approach I could also use the cleanup task to toss older bak files in the alt dir.
View 7 Replies
View Related
Aug 9, 2001
I've set up a maintenance plan to back up the database at a certain time of day. But it never seems to work, which I don't understand. I'm the creator of the database as well as the DBA with all rights. I can create a manual backup, and I have full access to the default backup directory on SQL server. I've also tried scheduling automatic backups, but it won't work from there either. I've tried pretty much everything to get this to work. I've checked the Maintenance Plan History for that particular Maintanance Plan, and it shows no evidence of failure--or any history of activity at all--nothing. It just ignores the maintenance plan entirely. What could be the problem here?
View 3 Replies
View Related
Jun 8, 2000
I defined a maintenance plan to do a backup, scheduled it for midnight, but no job ever ran. Is there some other step to do?
thanks!
View 2 Replies
View Related
Aug 16, 2006
I am currently using sql server 2005 standard edition. I just completed a maintenance plan using the wizard to perform a full backup nightly of a user database. I noticed there was no option to delete backups greater then a number of days old like we have with plans in sql server 2000. Is there a way of doing this through the plan in sql server 2005?
View 1 Replies
View Related
Jan 6, 2004
Hi, I have a scheduled maintenance plan that backs up my database (SQL 2000). The db is 108 Meg. Usually the backup files are around 89 Meg and the transaction log backups are around 4.5 Megs.
The problem is that every once a while the backup fails with the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC SQL Server Driver][SQL Server]Write on 'C:Program FilesMicrosoft SQL ServerMSSQLBACKUPCRM_db_200312230100.BAK' failed, status = 112. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.
What usually happens is that the last backup that has failed has a size of 9 or 8 Gigs!
My guess is that a backup file gets corrupt (the size increases to 8 Gigs) and the server runs out of space and wont be able to do more backups. However I cant figure out why the backup file size increases to and gets corrupt.
Can anyone help me?
View 4 Replies
View Related
Jul 22, 2015
In a recent attempt to keep the size of my transaction log files down I altered the schedule of my SQL Server log backups from running every 15 minutes from 07:00 to 19:00 to run every 15 minutes.My company also uses a Dell AppAssure application to also take backups. The backups are of the entire drives so I don't this will affect the size of my SQL log files but I did notice that AppAssure has a tick box to truncate the SQL logs so it made me wonder that it could affect the size of my log file. The Appassure backups currently run every 15 minutes from 07:00 to 19:00. I'm wondering if I would be able to maintain my log files at a smaller size if I ran this every 15 minutes from 07:00 to 07:00.
View 2 Replies
View Related
Jan 31, 2007
I'm the dba for a large company and have setup many maintenance plans for our sql servers. One of the options I have set is to delete old backups of databases and transaction logs after 2 days. For some reason, the old database backups are being deleted, but the old transaction logs backups are not. I have them being put in the same directory. Anybody have any ideas why this is happening and what if any fix is available?
View 1 Replies
View Related
Dec 8, 2006
Hi,
We have SAN for our SQL server and all of DB backup copy pointing to one the SAN volume(ex. T). We are moving the bkp copy from this SAN volume into remoteserver for restoring the backup. We are getting a lot of Time out during this time.
Is that copy process affect production time out?.
Thanks,
View 1 Replies
View Related
Jul 14, 2007
I am having frustration with issues I'm seeing within Development Studio. Here's the problem:
A fixed-width flat file is to be imported into SQL 2005. I am running from within BIDS on my workstation and send the data remotely to the server. It has over 200 fields, and I am attempting to import them into dimension tables, and one fact table. I had to manually create the columns on the flat file connection manager for this process, since the data file is delimited.
The SSIS solution has one project, and several independent data flow tasks within it. After I set up the fcm, I mapped fields using a flat file source, hooked up a derived column and sort transform. The idea was to use a 3rd party tool, TableDifference, to implement a Type 2 dimension. I was able to successfully build three data flow tasks that ran correctly. Later, as was building new data flow tasks, our project people decided to change the data types of several of the fields from Datetime to character, and also rename another 40+ fields. I dutifully kept my fcm synchronized, since column mappings are automatic when the names match; data type changes were also unavoidable on some of the columns.
The problem was noticed after I changed some of the columns in the fcm. My flat file source adapters on all the tasks needed to resolve changed metadata so I had to 'edit' and save them again. The derived column, sort, and downstream transforms also had to be updated. I saw strange behavior on previously tested data flow tasks. Some of them gave me errors even connecting to their sources. Other times I saw erroneous paths being taken which led to data being changed incorrectly on the dimension tables (new records being added even if there was an existing match, records being marked as expired incorrectly, etc.).
Are there known issues with editing a shared file connection manager after it has already been referred to within data flow tasks? It appears that the only way to avoid the problems I've seen is to either:
1. Create separate fcm's for each data flow task. Use 'filler' fields for the ranges of all the columns in between each referenced column. This way if you have to change anything with it, the only rework involved is limited to the data flow task using it.
2. Create one fcm for the entire package and NEVER change anything with it. If a column name is wrong, use a derived column to rename it in the data flow. If a data type changes, try to use a data conversion transform, but be sure to rename its output column so that it is not called 'data conversion.<field name>'. I think that's done in the Advanced Editor for the sort task.
My experience using BIDS has been hot and cold. It's nice to be able to quickly build the tasks you need, but it's as if the metadata (pipeline) is much too sensitive to change and requires a lot of touching up, either upstream at the source adapter level, or across data flow tasks when you make changes to the FCM. Maybe the problem is just limited to flat files...
Are there any tips or lessons learned from anyone on this? I've got another developer willing to create the FCM for other data feeds so I can copy/paste them into other projects. It would just be nice to know there was a bug fix or a good method to avoid all this 'rework' when you have to make changes to the FCM after the fact.
Thanks in advance.
View 1 Replies
View Related
Aug 17, 2007
Hello,
I have a question that I hope someone can clear up for me. I have come across a number of different suggestions on DB maintenance, for example reindexing with the following script:
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
My question is, doesn't the maintenance plan have this functionality inherent in it when you create the maintenance jobs to reindex? Is there a benefit to scripting things out vs just using the maintenance plan wizard for this sort of thing and any of the items it covers? I came from an Oracle background where this was a no-brainer but I am a bit confused on the choices with SQL Server.
Thanks.
View 1 Replies
View Related
Sep 13, 2006
Hello
I have a script ,which runs with osql
The script is :
osql -E
declare @cmd nvarchar(1000)
declare @cmd2 nvarchar(1000)
declare @state1 varchar(100)
declare @message varchar(100)
set @message = ''
-- Build command to determine state of SQLSERVERAGENT service on Master Server
SET @CMD = 'create table #state (state varchar(2000))' + char(10) +
'declare @cmdx varchar(1000)' + char(10) +
'insert into #state EXEC master..xp_servicecontrol ''''QueryState'''', ''''SQLSERVERAGENT''''' +
+ char(10) + 'select @state=state from #state' + char(10) +
'drop table #state'
-- Build command to execute command that determines state of service being monitored
set @cmd2 = 'declare @state varchar(100)' + char(10) +
'exec ' + rtrim(@@servername) + '.master.dbo.sp_executesql N''' + @CMD + ''',' +
'N''@state varchar(100) out'',' +
'@state out' + char(10) +
'set @state1 = @state'
-- Execute command and return state of service being monitored
exec master.dbo.sp_executesql @cmd2,N'@state1 varchar(100) out',@state1 out
-- Is the service that was monitored not
IF (UPPER(@state1) <> 'RUNNING.')
--if @state1 <1 'Running.'
begin
-- Display message that primary monitor is down
select @message = @message+char(13)+ @@servername + ' -' + 'Sql Server Agent Not Running'+char(13)
print 'Master server "' + rtrim(@@servername) + '" for monitoring is not available.'
exec master.dbo.xp_smtp_sendmail
It works fine when I run it on the command line prompt.
And I receive a mail , if the server agent is running.
But when I save it as bat file and try to run , it stops and doesnot even give an error.
Can anyone let me know what I can do.
Thanks
View 1 Replies
View Related
Jan 2, 2008
I am attempting to pull in data from a flat file data source that contains dates in the following format "01012007 10:22" which translates to Month Day Year and Military Time. I want to turn this into a DateTime format so that I can insert it into the proper column. I have a SQL statement which will do this (see bellow), but I can't figgure out how to run the statement on the data before it reaches its destination.
Can anyone help?
The code is:
Code Block
cast(convert(varchar(16),(substring( REPORT_RUN_DATE,1,2 ) + '/' + substring( REPORT_RUN_DATE,3,2 ) + '/' + substring( REPORT_RUN_DATE,5,10 )),1) as datetime) AS REPORT_RUN_DATE
View 9 Replies
View Related
Sep 2, 2015
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
View 2 Replies
View Related
May 10, 2007
I have a customer they are running raid 5 on a windows 2000 server one of the drives went bad. The customer replaced the drive and raid rebuilt the drive, every thing seamed to be fine but there is one database file that cannot be attached to SQL. The file is 15G so I know there is information the error states that the file is not a Primary file. Any clue on how to fix this?
mdf file size 5,738,944 KB
ldf file size 10,176 KB
View 4 Replies
View Related
Sep 2, 2007
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
View 3 Replies
View Related
Apr 16, 2007
I've been looking through this forum and can only guess that SQL2005 has changed in how backups and log file growth issues are handled. I tried the All Tasks feature from the GUI to perform a backup, however, the Truncate the Transaction log feature is greyed out so I can not select it. In SQL2000 I used the following to backup my databases and the log files were never a problem:
BACKUP DATABASE [DSS] TO DISK =
N'G:MSSQLBackupDSSbackup.BAK'
WITH INIT , NOUNLOAD , NAME = N'DSS backup',
SKIP , STATS = 10, NOFORMAT
Could someone assist me with a proper T-SQL to place in a job to backup my database and keep the log file under control and / or explain why the Truncate transaction log feature is greyed out?
Thanks!
View 5 Replies
View Related
Aug 9, 2007
I run two different types of backups on the same database.
A monthly full and nightly diffs appending to the same full file - file 'A'
A weekly full with 10 min trans log backups appending to same file - file 'B'- during working hours.
2 strategies, 2 backup files.
These are new strats that have gone live this week - tuesday in fact. The monthly and weekly both ran fine on tuesday as did the nightly diff and all the TS backups througout the day. last night - Weds - the nightly diff failed with the following error:
"
Executed as user: <USERNAME>. Cannot perform a differential backup for database "objectstore", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
"
Whilst the error is perfectly legible I dont understand what its implying - cant I add multiple Diff backups to the same full backup OR is my weekly / 10 mins on the same database (but to a diff backup file) ballsing things up for me somehow? If this is the case how come everything ran fine on tuesday night?
Any help gets beers.
THanks
Alastair Jones.
"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.
View 8 Replies
View Related
Aug 22, 2007
I'm working on a restore procedure for the case where all MDF filesare missing, but the LDF files are all intact. A full backup is doneevery 24 hours, and a log backup is done every 3 hours. Afterrestoring the last full + log backups, is it at all possible to usethe LDF files to recover data from that point up to a newer point intime?I've found a post which explains how to do this on SQL Server 2000<http://groups.google.com/group/comp...s.ms-sqlserver/browse_thread/thread/3ef5c7cbc0a83334/f3b0c70811d35ed7>, but step 4fails with the following error message:BACKUP LOG cannot be performed because there is no current databasebackup.
View 2 Replies
View Related
Aug 18, 2006
I'm trying to test out a restore scenario for a large database. It contains several files, each one is backed up when we are finished inserting data into it. No changes are made to the data after insert. All changes happen in tables in the primary file group.
So, I backup the primary file, the log, the first "partitioned" file, the log, the next "partitioned" file, the log, etc, etc, etc. Now, I delete one of the tables residing on one of the partitioned files and want to restore it. For arguments sake, let's say I have a primary file, and 5 partitioned files. I delete the table which was on file 3.
I'm having trouble wrapping my head around the steps to restore under these circumstances. I thought I understood it but, apparently not. When I try to restore, I'm told I must first backup the tail of the log. Having done that, I restore file 3, and all subsequent log files. Now, I'm right back where I was (missing the deleted table). When I try to roll back to a point in time before the delete...it claims to be successful but, nothing perceptable is different...??
Can someone help set me straight here?
View 10 Replies
View Related
May 31, 2015
Is it alright to move the .bak and .trn backups which are automatically created in a File Share Witness when a DB is added to an Availability Group? I did that and there were error IDs 1069 occurring, and the AG was unable to be brought up whenever I run a load intensive batch job ...
View 5 Replies
View Related
Mar 30, 2007
We have a set of databases some are fully read-only others have read-only file groups, is there any way to restore backups of these taken on an MSDE 2000 to an SQL Express 2005 instance?
When doing the inplace upgrade we change these to read-write before the upgrade and set them back after the upgrade.
These databases are used in the field by customers althought the controlled upgrade requires a backup before (and blocks if it fails) and tries a backup after if the post upgrade backup fails (due to disk space) we might need to recover from this odd situation.
The only solution I have is install MSDE some place restore to this then do the controlled upgrade again, any other ideas?
View 2 Replies
View Related
Mar 1, 2006
how can i attach the mdf file with a corrupted/deleted Log file
my log file is deleted how can i attach the mdf file
View 1 Replies
View Related
Mar 10, 2008
Hello World,
I'm new to SSIS and would like a little assistance getting started, if possible...
Here is what I want to do:
Check if file exist (C:DTS UpgradeFilexxx.txt) --->
Archive file (C:DTS UpgradeArchive) --->
Check if file has data (true or false)
AND/OR
If there are any good website that have good direction, let me know
Thanks in advance for your help!!!
View 5 Replies
View Related
Jul 31, 2014
I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?
I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.
View 1 Replies
View Related
Jul 6, 2015
For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.
Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?
View 5 Replies
View Related
Jun 23, 2015
I have a package in which there are only one Data flow Task and it has only three components. 1) Source , which is a SQL db 2) destination and 3) OLE DB Destination flat file Error output file. I want the error file to be created ONLY if there is any error while dumping the data into destination DB. But , the issue is, the error flat file is being created inspite of No error while dumping the data from Source to Destination.
View 5 Replies
View Related
May 14, 2015
I'm copying files to a folder with the naming convention as follows in the source folder:
CM_ABC_MY_TEST.txt
In the destination folder, this filename needs to appear as:
CM_XYZ_MY_TEST.txt
In my File System Task, I'm pretty sure I'm going to need an expression with a replace, substring, etc. But am having a hard time nailing down the exact syntax.
View 10 Replies
View Related
Jul 24, 2015
Need to know how I can get the dynamic filename created in the FlatFile destination for insert into a package audit table?
Scenario: Have created a package that successfully outputs Dynamiclly named flat files { Format: C:Test’Comms_File_’ + ‘User::FileNumber’+’_’+Date +’.txt’
E.g.: Comms_File_1_20150724.txt, Comms_File_2_20150724.txt etc} using Foreach Loop Container :
* Enumerator Set to: “Foreach ADO Enumerator” with the ADO object source variable selected to identify how many total loop iterations there are i.e. Let’s say 4 thus 4 files to be created
*Variable Mappings : added the User::FileNumber – indicates which file number current loop iteration is i.e. 1,2,3,4
For the DataFlow task have a OLDBSource and a FlatFile Destination where Flat File ConnectionString is set up as:
@[User::Output_Path] + "Comms_File"+ @[User:: FileNumber] +"_" + replace((DT_WSTR, 10) (DT_DBDATE) GETDATE(),"-","")+ ".txt"
All this successfully creates these 4 files:
Comms_File_1_20150724.txt, Comms_File_2_20150724.txt, Comms_File_3_20150724.txt, Comms_File_4_20150724.txt
Now the QUESTION is how do I get these filenames as I need to insert them into a DB Audittable. The audit table looks like this:
CREATE TABLE dbo.MMMAudit
(
AuditID INT IDENTITY(1, 1) NOT NULL,
PackageName VARCHAR(100) NULL,
FileName VARCHAR(100) NULL,
LoadTime DATETIME NULL,
NumberofRecords INT NULL
)
To save the Filename & how many records in each file in our Audit Table, am using an Execute SQL Task and configuring it as this:
Execute SQL Task
Parameter mapping - Mapped the User Variable (RecordsInserted) and System Variable( PackageName) to Insert statement as shown below
SQLStatement: INSERT INTO [dbo].[MMMAudit] (
PackageName,NumerofRecords,LoadTime)
(?,?.GETDATE)
Again this all works terrific & populates the dbo.MMMAudit table as shown below BUT I also need to insert the respsctive file name – How do I do that?
AuditID PackageName FileName NumberOfRecords
1 MMM NULL 12
2 MMM NULL 23
3 MMM NULL 14
4 MMM NULL 1
View 2 Replies
View Related
