SQL 2005 And Growing LDF - How To Safely Shink It And Keep It Shrunk?
Aug 13, 2007
Techs,
Every morning I come into find my LDF is about 70 gigs. So I shrink it based on the percentage it will allow because that seems safe, because this is a critical production server, which reduces the size down to 65 gigs. However, I return the next morning to find it has grown back to the original size plus some. How can I safely shrink this monster and keep it manageable?
Many thanks in advance.
View 3 Replies
ADVERTISEMENT
Oct 18, 2007
Hi
We have a bunch of servers running server 2000 & 2003 along with many sql server (versions 2000 & 2005) databases in a production environment pulling transactions then doing warehousing & reporting.
An audit has shown up 1 production server using English_US 'mmddyyyy' system date formats, all others being English_Australian 'ddmmyyyy'.
Is it safe to simply change the regional settings on this English_US server to English_Australian or will it mess up the data in our SQL 2005 databases? I've not been able to get a definitive answer from anyone yet!
many thanks in advance; GaryP
View 1 Replies
View Related
Feb 29, 2008
Hi,I am using visual studio 2005 with sql server 2005.I would like to know what is the way of coping with an increasing database?Any answer?
View 1 Replies
View Related
Jul 13, 2004
I was thinking that this question probably comes up a lot...but my forum searches didn't turn anything up, so here goes:
I have an id field that auto increments (identity) for each record.
I insert a record.
How can I safely extract the id field of the record for use in another query, avoiding the possiblity that another record is inserted before I can get at it?
Thanks,
G
View 6 Replies
View Related
Apr 8, 2007
Hello,
For one of my clients I have built 2 applications. The 1st one is a windows based application that is used for production and employee scheduling. The data for this application is stored in a SQL Express database. The 2nd one is a ASP.NET 2.0 site that contains the clients website and a portal page. The portal page uses forms authentication and redirects a customer to a page that shows the production schedule for this customer. So I have 2 kind of users: local (LAN) and web. Both using the same database.
For test purposes the site is currently hosted on the clients SBS 2003 server (this is the only server). However I don't think it is wise to host the public website on this server. What is the best configuration to host the public website safely? I read about putting a webserver in a DMZ. But I need a connection with the SQL Express server to retrieve the data for the portal section. As well for authenticating the customer and for retrieving the schedule data.
I'm currently using Windows Authentication on the SQL server. Can this still be used when the webserver is in the DMZ?
If I understand it correctly the webserver in the DMZ is not part of the local domain? Also could remote hosting be an option at all?
Any help and advice would be highly appreciated.
View 1 Replies
View Related
Sep 25, 2006
We recently installed SQL server 2005 on a couple of our servers. I use Visual Basic 6.0 at the moment and use ADO to connect to our various SQL servers.
I recently discovered on one of the new servers, that every time my programs runs, (every 4 minutes for 12 hours a day) the SQL process shown in task manager grows by 1-10 Megs.
The SQL process was at 776,912K when I rebooted this afternoon. It started back up at 106,120K.
I am not doing anything differently than I did when my programs were talking to SQL 2000, and I have never seen this memory leak issue. Is there something extra I need to do in SQL 2005 to finish/clear these SQL queries and not bog down SQL's memory?
An example of how I would connect and do a SQL transaction:
Dim cn as ADODB.Connection
Dim rs as ADODB.RecordSet
Set cn = New ADODB.Connection
Set rs = New ADODB.Connection
cn.Open strConnect
select1 = "select firstName, lastName from clients"
rs.Open select1, cn, adOpenKeyset, adLockOptimistic
If rs.EOF = False Then
rs.AddNew
End If
rs!firstName = Trim(Text1(0))
rs!lastName = Trim(Text1(1))
rs.Update
rs.Close
cn.Close
At the end of the program's run I would:
Set cn = Nothing
Set rs = Nothing
View 3 Replies
View Related
Aug 22, 2001
I upgraded from SQL 6.5 to SQL 7 last month, and so far, everything's
been going fine.
However, I'm not using my old SQL 6.5 backup scripts, which, when the
backup was done, would dump the transaction log with TRUNCATE_ONLY, shrinking
the log size.
My SQL 7 server is set up with a Maintenance Plan which does everything,
including backup, but the log file seems to be growing and growing. I'm
up to 4.5 gigs now, for a database with a data file of 3.5 gigs.
How do I "dump transaction with TRUNCATE_ONLY" on a SQL 7 database?
Thanks,
Todd Wallace
View 3 Replies
View Related
Mar 1, 2004
I have merge replication setup up for 6 SQLCE Subscribers.
I have noticed that the MSmerge_tombstone table is growing
at a fast rate regardless of any changes to the data in
the database. It seems to be consistantly adding 50 rows
of data to the table every 2 minutes. As the table grows
it causes the SQLCE subscirbers to fail with the following
message:
ERROR: -2147467259
SQL Server Reconciler failed: Run
ERROR: -2147200925
: Failed to enumerate changes in the filtered
articles.
ERROR: 0
: {call sp_MSsetupbelongs
(?,?,?,?,?,0,?,?,1,?,?,?,?,?,?)}
ERROR: 0
: The merge process timed out while executing a
query. Reconfigure the QueryTimeout parameter and retry
the operation.
I'm sure that this is due to the size of the
MSmerge_tombstone.
Should the MSmerge_tombstone table grow at this rate?
36,000 rows every 24hrs!
I understand there is the sp_mergecleanupmetadata Stored
procedure but if i use this does that mean that because i
have to reinitialise all the subscribers, they are going
to have to pull down the whole subscription again.
I have since Changed a settings to make subscription
expiration date to 8 days instead of never expires but
we're still getting 50 rows added every 2 minutes
SQL SERVER 2000 SP3
Hope someone can shed some light on this for me.
Thanks.
.
View 5 Replies
View Related
Mar 28, 2000
I wonder if anyone could explain why when monitoring the transaction log size it doesn't appear to be growing! I'm using the following code to test image data types with logging.. I've got 'Truncate log on Checkpoint' switched off and 'Select into Bulk copy' also switched off.
Running the following code I would expect to see the transaction log grow and grow and grow... Monitoring it using perfmon indicates that it isn't in fact logging...
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON p.pub_id = pr.pub_id
AND p.pub_name = 'New Moon Books'
declare @Loop int
select @Loop = 0
While @Loop <= 10000
BEGIN
WRITETEXT pub_info.pr_info @ptrval
with log
'New Moon Books (NMB) '
select @Loop = @Loop + 1
END
View 1 Replies
View Related
Oct 27, 1999
Hello,
Does 7.0 store db Growth rate information?
I am looking for information that tells me how fast a db is growing in MB and or percentages over a given period of time, ie weekly, monthly, yearly etc. Either in real numbers or estimates. Does 7.0 already store something like this or do I need to create some code for this?
Or does someone have something like this already coded that they would be willing to share?
Thank you in advance.
Troy
View 2 Replies
View Related
Feb 2, 2000
In SQL Server 7.0 sp1 (NT 4.0 sp5) I have a server that
has a tempdb database that continues to grow. This server
contains the database for SMS. Over the weekend, the tempdb
had grown so much that it filled up the drive (37GB). I have
shrunk it down to a much more reasonable size and put a limit
on how large it can grow. I'm noticing today that it is
beginning to grow again. Is there a way I can look at the
information that is in tempdb right now? I have to think
that there are open transactions for some reason that can't
commit. I know that tempdb gets cleared out when SQL Server
is restarted, but I can't be restarting it this often.
On Microsoft's website, I did find an article about SMS Y2K
queries using large amounts of Tempdb and failing to complete.
The solution they have in this article Q234912 is to install
SMS sp1 which is already installed.
I haven't been able to find any other useful information yet
on this problem. I would appreciate any help you can offer.
Thanks!
Toni
View 2 Replies
View Related
Jan 24, 2003
Greetings,
My database has a situation where my transaction log is growing out of control. However I have not been able to figure out where any memory leaks are occuring.
Is there a way to monitor the database in order to find out at when the tlog is growing. Or even better, what sql is being executed that is causing this unreasonable tlog growth?
Thanks a million,
Roger
View 2 Replies
View Related
Mar 6, 2007
Hi,
I'm getting this SQL server log...
The log file for database 'P5_Nextel' is full. Back up the transaction log for the database to free up some log space
What i'm doing is, i just resizing the space allocated, but the problem is my disk is now out of space. How can i prevent this kind of problem without adding a new disk? Is there any other way?
Please advise.
Thanks!
View 4 Replies
View Related
Aug 7, 2007
Why does a log (.ldf) file keep growing and growing and growing? Is this related to the fact that the scheduled Maintenance keeps failing due to exclusive access problems?
SQL Server 2000 Std.
Thanks!
View 7 Replies
View Related
Apr 9, 2006
Hi there,
My tempdb is growing from its normal size of 800MB to 2GB.
I've been shrinking it using dbcc shinkfile/ dbcc shrinkdatabase.
Everytime I run the command, immediately it says that the execution is completed and successful. However, when I checked the disk space, it remained the same, as though no shrinking is done. Can anyone help?
Also, it was published that restarting the SQL server can re-create tempdb from scratch. I've tried it too, the tempdb just wont go back to its normal allocation. My constraint is limited disk space, would appreciate any good samaritan to give me some help here.
Thanks in advance!
View 20 Replies
View Related
Feb 29, 2008
I am having problems with the transaction log growing. I set up a maintenance plan to backup every 15 minutes. The recovery model is set to full. It is set to auto grow by 10 percent with unrestricted file growth. The space allocated is 2mb. Should this be set higher? It seems whenever I unchecked the auto shrink, the log is growing larger. The command I use to check is:
SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
GO
This is the log size and the log space used.
1.492187538.02356
This has grown 4 percent since yesterday. Are there any good practices to maintain these log files?Any help would be appreciated.
msr976
View 1 Replies
View Related
Aug 24, 2006
Hi,We have created a SQL server 2000 database. We observe that thetransaction log keeps growing over time. We are now about to run out ofspace. We have been periodically shrinking the database. Neverthelessthe size has increased. I would imagine that a transaction log can beeliminated if we stop the database. Can that be done? Is there a way tocompletely wipe off the transaction log?Thanks,Yash
View 4 Replies
View Related
Jun 16, 2008
We have a database that's growing pretty fast because of firewall logs. We need the data available via an asp.net application. I don't have great experience with SQL other than installing and doing some development as a back end, so i'm wondering if there's a general rule of thumb of database size, when you should start breaking it out into smaller segments? if so, what are some good practices?
View 3 Replies
View Related
May 28, 2002
I have a database that seems to have grown out of control. I have tried deleting tables, but that has not really reduced the size. What could have caused the database to grow this big and what can I do to reduce it's size. I have backed up, truncated the logs, ran the shrink database command, all to no avail. Pleas help.
View 5 Replies
View Related
Nov 8, 2000
Hi,
my log files are growing like anything. One of my log file size is 20GB.
How i have to reduce the log file size.
If i run DBCC command is it come backs...
Pls tell me the way how i have to find the free space and reduce logsizes.
After taking backups also my log file sizes are not reducing.
Thanks!
Kavira
View 2 Replies
View Related
Apr 10, 2000
Does anyone know at what point SQL Server 7.0 decides to grow the database when the autogrow option is set? Our site just went down for 45 minutes because the growing process was taking too long as compared to the data coming in, so the device filled up.
Ray? Craig? You guys seem to know all, so jobs.com appreciates your input...
View 7 Replies
View Related
Mar 12, 2003
I have to admit, I'm usually using the MySql database, but in this particular case I have to use MSSQL 2000.
Over to my problem.
I'm building a web-based system (who isn't these days) in which the user types arbitrary information that is published when the user pushes the save button. Nothing new about that.
Here comes the tricky part, when the user wants to edit an existing item I copy all information in the database and sets the id of the 'edit-copy' to the negative value ( id 45 becomes id -45 for the edit-copy). This is also done on all items in other tables that is connected to the main item.
This way I get a copy that the user may edit without messing up the published information. When the user is done I either delete all the negative items (cancel) or delete the positive items and update the negative to become positive (save).
So far so good, allmost... my problem is that the transaction log grows tremendously.
Is there any other way to accomplish a safe edit that doesn't affect the transaction log as much as my current solution?
Could I be doing something wrong when updateing or deleteing my items?
View 1 Replies
View Related
Sep 10, 2003
I notice this morning that my tempdb grows very fast. I have 26GB in my
hardrive and all the space occupied by tempdb and finaly the qeury got failed due to 0 space in hardrive and there is no space to grow tempdb.
The select query supposed to bring about 40000 rows.
I ran this same query in different server that is not growing even 1 mb.
I checked the tempdb option the Trunc log on checkpoint is true.
Why this problem happening ?.
I have just dbo permission to access all the database.
Do you have any advice regarding this?.
Thanks,
Ravi
View 3 Replies
View Related
May 16, 2006
I have a database of 22 gb in sql 2000, my database option is set to full recovery mode, the problem i'm having is the tran log is growing too fast, this morning it was 24 gb, more than the database size. Can anyone help how I can keep it in a managable size?
Thanks in advance!!
View 2 Replies
View Related
Nov 15, 2004
Hi all
I have a DB with 1 data file, 1 log file and 1 index file.
data file is 3 GB but index file is 12 GB.
Index file is growing big day by day.
This cause performance of DB down.
What should I do to prevent index file become bigger and size of index file smaller?
Thanks in advanced
Thi Nguyen
View 5 Replies
View Related
Nov 8, 2012
My log file was 2x the size of my actual Database which is obviously too large on a DEV box. I know that my data can be easily recovered so I actually do not even want/need a log file.
After doing some investigation I found that I should turn my database into "Simple Recovery Mode" and after this I used a few scripts to truncate my log file. Things at this point looked great!
Unfortunately my log File is still growing even with this 'simple recovery mode'. So how do I stop this craziness from occurring?
I even unchecked the box 'allow autogrowth' on the database! However, I eventually get errors when creating records in the system because it complains about running out of room in the log file.
Code:
The transaction log for database 'ReportingDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
View 8 Replies
View Related
Jan 28, 2014
The transaction log of a database grows until it runs out of disk space. If disk space is full, all databases on instance may get problems. Because of this i have set a limitation on how much it may grow, up to 40 GB. It grows in steps of 100 MB. It reaches its limit a couple of times a week, causing the application to hang.
The database file itself is about 2,3 GB large.
The SQL version is 10.50.4276, SQL2008R2 SP2.
The recovery model is FULL.
I have a backupjob that runs a FULL Backup at midnight.And a backupjob that runs a LOG backup every 30 minutes.Both finishes with success. However, the transaction log is never truncated, the unused space is never released.
I have checked for "long running jobs", it sometimes sys "backup_log", sometimes "active_transaction".
Could a workaround be to set the recovery mode to simple, and create a full-backup job that runs every 30 minutes for this database? It is a critical database....
View 4 Replies
View Related
Mar 22, 2004
Hi all.
I have a db application that has been running fine for months. Volumes have been gradually increasing and one day the system locked up.
A stored proc that typically ran in 3 or 4 minutes never returned. The tempdb kept expanding to fill available disk space (100GB). This was the offending statement inside the stored proc:
INSERT INTO cpp (CPPDate,MerchantLink,ReportNumber,FromDate,ToDate ,TransThreshold,DayThreshold,CPPType)
SELECT S.CPPDate,S.MerchantLink,s.ReportNumber, s.FromDate,s.ToDate, S.OccurThresh,s.DaysThresh,'D'
FROM #stuff S, Trans T with (nolock), Supplier P with (nolock)
where T.MerchantNumber in (SELECT MerchantNumber FROM Merchant WHERE MerchantLink = s.MerchantLink)
AND T.TranDate >= S.FromDate
AND T.TranDate <= S.ToDate
AND T.LoadDate <= @ReportDate2
AND (T.SupplierNumber = P.SupplierNumber
AND T.IncludeInCpp = 'Y'
AND P.CountryNumber IN (SELECT CountryNumber FROM REPORTCOMBO WHERE ReportNumber = s.ReportNumber))
GROUP BY CPPDate,Merchantlink,ReportNumber, FromDate,ToDate, OccurThresh, DaysThresh
HAVING COUNT(DISTINCT T.AccountNumber) >= OccurThresh
I realize that a "group by" uses the tempdb, but can't figure out why it would go away rather than returning an error.
I have a workaround in place now. I split this big query into several steps using a cursor. (slower and clumsier, but it works) Statistics are updated daily, i have tried defragging, and reindexing with no success.
Any thoughts would be appreciated. If you need any more details, please let me know.
Thanks in advance.
View 3 Replies
View Related
May 21, 2007
I have a sql2005 transactional replication replicating several databases between 4 servers, all of the replication are working fine except 1 that is going to a sql2000 sp4 enterprise database. The undistributed commands grows by several hundred thousand records a day, yet all of the records counts match exactly. At first this server was having a lot of lock issues, so i thought maybe that was the cause, but over the weekend i dropped replication and resnapshotted it. Without any activity ocurring on this database this weekend it continued to grow at the same rate
View 4 Replies
View Related
Apr 30, 2007
Hi there,
We have a Principle, Mirror and Witness set-up and all is working fine, however, the transaction logs for a few large databases just keep growing over a the course of the month until the disk is full. As I understand it, and having tried you can't dump the transaction logs while mirroring is configured, is there any way at all to commit and truncate the logs while mirroring is running or do I have to manually remove the mirroring each month, dump the transaction logs and then re-enable it again after doing the backup/restore?
The databases in question are about 6GB in data size and the transaction logs can grow to be about 60GB in a month.
Would a normal SQL Server 2005 backup truncate the logs if I configured this? At the moment we use Litespeed for SQL server for nightly backups.
Any advice would be very helpful.
Thanks
Ed
View 5 Replies
View Related
Nov 15, 2007
I am showing a Multi Value parameter on a Report, in a textbox that has CanGrow = True.
This is fine when there are only a few value in the parameter, but when many values have been selected, the fact that the textbox grows automatically to accommodate the contents means that this textbox can become quite large.
I am using the value of:
Join(Parameters!ParameterName.Label,", ")
Is there any way to either limit the number of values of a parameter when displaying in this way, or of setting a maximum size for an automatically grow textbox?
Or anything else that would solve this issue?
Thank you very much in advance!
View 1 Replies
View Related
Dec 1, 2007
Templog is growing 1 GB per hour.
I've read some articles about this issue, that talk about how to shrink it.
In this case I need to find out what and why this is happening
How can I monitore it?
I know, sometimes, I exaggerate in using temporary files, in order to make reports faster.
The Tempdb size is normal.
Thanks
View 4 Replies
View Related
Jan 14, 2008
Hi.
I know this question has been posted before but after reading some other post I'm still a bit confuse of what to do?
In my database, I have a 3Mb data file and the transaction log file has growed to 500Mb and keep growing... Can you please advise what I should do to reduce it size and/or stop it from continue growing?
Thanks for your help.
View 1 Replies
View Related
