I'm trying to make sure my database options are set up properly. Our database is used for our Decision Support System. The data is loaded in once a day. Should I have the truncate log on checkpoint on and should I limit the size that the log grows to. I just had to shrink the log. It grew so much that it took up all the space left. If I have the truncate log on checkpoint do I just have to issue the checkpoint command in order for it to truncate the transaction log?
I have a database that has Truncate on Checkpoint set for the Log file. The Log file is set to AutoGrow. Is it necessary to to run dbcc shrinkdb (or the like) to get Log file to contract? Is there any harm in not contracting the Log file? I'm looking for best efficiency and least-likely-to-fail path as DB sits 'really remote' and there is little opportunity for observation.
Does anyone have any recommendations on re-indexing? I have one table that bears the most growth. It has a clustered index. What would be a suitable data point to watch? I run a SP to save DBCC SHOWCONTIG info along with the duration of a test query, but haven't seen a clear breakover point.
Can anyone assist with this problem. Every now and then my overnight backups (backup Exec) fail due to the truncate log on checkpoint being enabled. This occasionally occurs on Master MSDB databases. I have unchecked the truncate log on checkpoint box numerous times and the backups work fine. Then mysteriously the box is checked again and the backups fail once more. I am stuck as to why this can happen. Is there a generic stored proceedure that checks this box ?
The log on one of my databases keeps filling up, even though I have it set to truncate on checkpoint. the only real difference between this database and the others on my server is that it is built from the dump of another database (on another server) where the tables are marked for replication.
I'm wondering if the fact it is built from a replicating database could be causing this. I've noticed I can't drop any of the table, even though my database isn't set to replicate (or publish).
two questions 1) Any ideas? 2) Is there anyway I can make my server realize I'm not replicating so it will let me drop those tables? (nothing in Enterprise manager indicates that my database is replicating or publishing).
I am new to SQL Server 7 and have inherited a server built by a consultant that is no longer here. I have noticed that the system databases (master, msdb & model) are completed backed up on a nightly basis and are all set with truncate log on checkpoint. Is this the proper way to have things set up?
Still I have problem with the DBCC DBReindex which results in large size transaction log equivalent to the backup even after I tried the checkpoint command after this job doesn't change anything.
(as we cannot change to DBCC INDEXDEFRAG for all the indexes. )
Is that Ok if we have a truncate log on checkpoint set to true when this job runs and make the truncate log on checkpoint to false after the job
I've just inherited the job of looking after our SQL server (2012). The server works fine but I am a bit concerned about the backup strategy we have in place. The current backup strategy is a SQL script that runs every 2 nights and backs up to an ISCSI box elsewhere on the network. One of the databases on the server is currently at 303GB, which will take about a full working day to restore to another server if this one fails. I am just looking at other ways which could reduce the amount of time to restore this if this server was to fail. i heard so people talking about sql clusters and configuring replication on posts by others, which i will look into if its the way to go, but i guess I am just looking what others would do if they were in the same position with such a large database.
I have two very simple stored procedures that truncate (clear out) the rows of my 'Transactions' table, then import rows from another table into it, thus updating the table with the appropriate data. I would like to call these two stored procedures from a web form - asp.net 2.0 web page, using 2 buttons or possibly 1 button if I could, but can't find any information on how to do this. here is code for: 1. my button for 'truncate' <asp:Button ID="Truncate" runat="server" Text="Truncate" style="left: 45px; position: relative; top: -2px" Font-Size="8pt" /><br /> 2. my stored procedure for 'truncate' ALTER PROCEDURE dbo.TruncateTrans AS SET NOCOUNT ON; Truncate Table Transactions;
2. my button click event and page load event handler for the web form;
Imports System.Data Imports System.Data.SqlClientPartial Class Admin_Default Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load End SubProtected Sub Truncate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Truncate.Click End Sub End Class
Also, here is my other stored procedure called updateTransact. how can i execute these using the web form and buttons? or is there another way to do this?
ALTER PROCEDURE [dbo].[UpdateTransAct] AS BEGIN SET NOCOUNT ON; SELECT * FROM Transactions INNER JOIN UpdateTrans ON Transactions.TransID = UpdateTrans.TransID INSERT INTO Transactions (Account, TransID, TransType, Transkind, Status, Amount, Transdate, Note, Ticker, TickerType, BaseDate, BasePrice, UnitPrice, Shares, UnitsPerContract, Commission, StopTypes, FixedValue, Strategy, WithHolding, SavUnitPrice, SavCommis, ReInvestDividends, ReInvestCapGains, StrategyType, OrderID, ReferenceID, Fees) SELECT Account, TransID, TransType, Transkind, Status, Amount, Transdate, Note, Ticker, TickerType, BaseDate, BasePrice, UnitPrice, Shares, UnitsPerContract, Commission, StopTypes, FixedValue, Strategy, WithHolding, SavUnitPrice, SavCommis, ReInvestDividends, ReInvestCapGains, StrategyType, OrderID, ReferenceID, Fees FROM UpdateTrans WHERE (TransType= 'C' OR TransType='O') ORDER BY TransDate END
Hello,I hope you can help me.We have a SQL Server 2000 database at work, (which works with a VB6frontend) which grew to a considerable size, so one of my past colleaguessent me this truncate statement to use on the database.I ran it like this, and all appeared to be well, it shrank the database,Shortly afterwards, after I ran this I noticed something not quite right.for example, one of the tables we keep is a User_Admin table which has 50records in there, when I accessed this using the frontend it only showed 1record, and this happened with every table in the database accessed by thefrontend.The data in the tables is still there, but only shows one record through thefrontend.Can anyone help me, and check this statement I ran, as its weird that allthis happened after I ran this statement.Thanks,Jayne Hegersp_dboption PBTDEV,'trunc. log on chkpt.', truecheckpoint--sp_dboption sadev,'trunc. log on chkpt.', falsesp_dboption PBTDEV,'autoshrink', true--sp_dboption sadev,'autoshrink', falseDBCC SHRINKDATABASE (PBTDEV, NOTRUNCATE)
I want to truncate all tables present in the particular database, Is there any simple way to do it? or do I have to do it on individula basis (table by table)?
I'm using SQL Server 2012 R2 and am working on configuring vendor access to a particular DB. I have a test db & (what will eventually be) the production DB. I've configured security for the test DB and want to back that up, then restore it (including all settings) to the prod one, renaming it to the prod DB name.
I have a SQL Server 2K db with some 10 tables and I want to setup a nightly job to truncate all db records which are more than 15 days old. Can anyone provide me with steps involved? Any help will be highly appreciated.
Is it possible to truncate Transaction Log and Shrink DATABASE while the database is being used by users or the database becomes unuvailable during this operations?
We have SQL Server 2012 running on Windows 2008 Server. We need to copy five databases from our 'sandbox' to our test server and then to our production server. The database backup file sizes are 3 MB, 20 MB, 344 MB, 645 MB and 17 GB. We are planning on using the backup and restore method since we already have full backups and the scripts to recreate the logins/users/permissions. We believe this method provides more flexibility and control over the process. However, we have a few jobs, maintenance plans and ssis packages.
To get the jobs to the new server instance, the plan is to script out the jobs on the 'sandbox' and execute the scripts on the test server instance. Is this the best or only way to handle the jobs?How to get these maintenance plans to the new server instance? (There is no 'script out' maintenance plan option.) We may have to just recreate them on the new server instance. Is this the best or only way to handle the maintenance plans?
We have a few ssis packages. How to handle getting the ssis packages over to the new server instance (using the backup restore method)? These packages use the Project Deployment Model. Therefore, should we restore the SSISDB or open up the package file using VSS on the new server instance and change the connection information to point to the new server instance.
Just wondering if there is any reason or advantage to use the Detach and Attach method or Copy Database Wizard method? I have read where the Copy Database Wizard method handles the database's dependent objects like logins, jobs, maintenance plans, user-defined error messages and shared objects from the master database. Are there any other move/copy database methods to consider? Just trying to make sure we have thought out everything and are using the best method to copy our databases over to another server instance.
Hi,I need to set up a generic table in Oracle that allows users to enterdata that can later be retrieved in reports.I was thinking of a 3 column table in this structure:DATE,ITEM,VALUEfiguring that you can store anything in this way because ITEM can beanything and you can have multiple instances of a particular value byhaving a new date.Does that seem reasonable?In any case, what are my user front end options? I was thinking aMicrosoft Access database but its not ideal because not everyone herehas Access?Can you do it with Excel? Or what other options are there as a simpleuser front end to enter the data?Thanks for any advice.Kim
I was working on my Lab Environment and i had a DB which was mirrored in synchronous mode without witness server. I truncated one of the tables in this DB and did a failover to the mirror server. The table got truncated in the mirror too. Though we know that truncate is a non logged operation how does this mechanism work? Can someone please expain me??? Thanks in advance. Your replies will be very much helpful to me.
With a database size of almost 2 GB, I run the 'truncate table eventlog command' which completes successfully, but the database size only decreases by about 10 MB so stays too large - indeed the number of rows in the eventlog table is minimal, but the otehr tables in this database don't show such an amount of tables large enough to cause the size issue either. What could be the reason and how can I reduce it (possibly truncating another table but then which one, how could I determine which is too large and needs truncating?).
I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.
For example
EmployeeDetail table references Employee table DepartmentDetail table references Department table Department table references Employee table
I installed SQL Server 2005 Developer Edition. When i create a new database (using the "New Database" dialog) i cannot set the new database's compatibility level to "SQL Server 2005(90)" because this option is not in the "dropdown list". the only items shown are: "SQL Server 7.0(70)" and "SQL Server 2000(80)". I set the owner to "sa". How do i get "SQL Server 2005(90)" in my "compatibility level" drop down list? Is this an installation option that i missed? Thanks in advance for any assistance!
I would like to know what happens if i shrink the database with truncate only option and do a full backup or transaction log backup ? are the full backup or transaction log backup valid? I know that the performance of the database is bad if i shrink the database. What happens to full backup or transaction log backups?
Please forgive the elementary nature of my question, but could someone please explain the differences between these two database backup types:
1. Log backup 2. Log backup no truncate
From what I understand and have read, the "no truncate" backup method keeps the entire transaction log indefinitely. Using the truncation method, the transaction log is either 1) compressed or 2) cleaned up so that any completed transactions are removed from the log. Which one of these is true?
And, for the big question: is it better to run a backup of the transaction log with truncation or not? Our current backup scheme is similar to the following:
Full backup every 24 hours transaction log backup every hour with no truncation
Should we insert a truncation backup somewhere in here? What is the danger of removing (or compressing) parts of the transaction log? Will this affect the restore process?
I would like to checkpoint my transaction log every night before full backup. Would this affect the transaction log sequence in the event of a restore.
Disaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition --We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately --Recovery needs to happen within 1 hour (Not sure that this is realistic -- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable 2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances 3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
Has anybody encountered this situation before? DB on SQL Server 2000 SP4 with trunc log on chkpt option turned on. Checkpoint trace flags were turned on but noticing no checkpoints are being done on one specific DB resulting into growing transaction log. No open transactions.
I see a line in sys.sysprocesses. The process's status is suspend and the command is CHECKPOINT. I have the information here exactly as it is on my monitor. It seems is consuming hi cpu. What should I do? [/code] spid: 10 kpid: 7416 block: 0 waittype: 0x0081 waittime: 232546 lastwaittype: CHECKPOINT_QUEUE waitresource: dbid: 1 uid: 1 cpu: 427046 physical_io: 36695 memusage: 0 login_time: 2007-04-04 10:01:32.787 lastbatch: 2007-04-04 10:01:32.787 ecid: 0 open_tran: 0 status: suspended sid:0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 hostname: program_name: hostprocess: cmd:CHECKPOINT nt_domain: nt_username: loginame: sa [/code]
I have a package that uses checkpoint restart. It is resposible for truncatings many sets of tables and then loading them. There are several ExecuteSQL tasks to truncate the tables and several corresponding data flows to accomplish the loads.
If a load fails I want the corresponding truncate task to be part of the restart otherwise duplicate data may be loaded. Normally, SSIS will start at the failed task. I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
With 2005 SP1. Have built a SSIS package that successfully saves a checkpoint file and sometimes successfully restarts. (I've also built some others that are 100% reliable).
On the unsuccessful restart it appears as though the failed steps and subsequent steps do not execute. the package appears to "complete" though and the checpoint file is removed as though everything is fine.
On a successful restart the failed step reexcutes and everything works fine.
The issue appears that when a failed step finishes at the same time as a successful step finishes there is contention in the process that writes the checkpoint file out and the checkpoint file is corrupt. The failing step runs in parallel with a successful step and the execution times are very similar so task A may complete before or after task B.
Contents of a good checkpoint file follows <DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:PackageID="{3BFFF2F9-74BA-4CE9-8435-81CC198E8144}"><DTS:Variables DTS:ContID="{3BFFF2F9-74BA-4CE9-8435-81CC198E8144}"/><DTS:Container DTS:ContID="{3655F83D-5EA5-4F16-9B8F-520582A1229A}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DB2D7A57-D405-4B11-AF4A-41B331EE3F15}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DFC6A95F-CCFA-4FD9-B604-FCBD722B47D8}" DTS:Result="0" DTS:PrecedenceMap="YYY"/></DTS:Checkpoint>
I've set up a number of jobs (not a maintenance plan) via a script in SQL 2005. These jobs do the following:
1) Full backup every sunday night 2) Differential backup every weeknight 3) Log backup every hour
The database is obviously in the full recovery model.
The backups all seem to be running, with one issue - the log file is still growing and is not being truncated. I was under the impression that a log backup should result in the log being truncated after each full backup. However, this does not seem to be the case.
Is there anything obvious I've missed that needs to be set up, or is there a way I can check that the full backup is actually setting the appropriate checkpoint and that the log backups are 'seeing' these checkpoints?
If i have 3 Tasks in my control flow with checkpoint enabled and the transactionoption of the tasks is required,
Transaction option of the package is supported.
if the second task fails , the package restart from the first task when its running again instead of using the checkpoint and begin from the second task
I am DB Developer (not admin), excuse me if this is a silly question.
I don't know much about CHECKPOINT background But I feel, this process is slowing down performance of my sps which runs slower than normal in some cases.
Especially when I see any of my process goes in the suspended mode and its wait type is SLEEP_BPOOL_FLUSH and CHECKPOINTs process is also suspended and its wait type is CHECKPOINT_QUEUE.
More important anything else is... this background process (which I always find its spid is 11) BLOCKS all other user processes when it goes into suspended mode and its wait type is SLEEP_BPOOL_FLUSH
I dont know my analysis is correct (claiming checkpoint as culprit), need experts advice and help
can someone give info on checkpoint and how this effect server performance
We're running the Microsoft product SMS 2003 SP1 for software deployment, patching, hardware inventory, etc. The back-end is SQL 2000 Enterprise SP4 which is installed on the same box as the SMS 2003 SP1 product, and the DB is 145GB's.
We started noticing that the server would freeze every minute or so for 30 seconds. We started logging stats via perfmon and saw that the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 for 30 seconds at the same time the freezing occurred. I have determined that this is occurring during the checkpoint. The recovery interval option is set to the default of 0 on SQL, when I changed the setting to every 5 minutes, the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 every 5 minutes and would subside after 2 minutes. I understand the need for the checkpoint / recovery interval option, but don't believe this high average disk queue length should be occurring.
Does anyone know why this is happening and how to fix this ? The freezing of the box while checkpointing is killing me.
