Control The Filesize Of Trans Log
Dec 18, 2001
Hi all,
Just wondering if the virtual filesize of the transaction log can be changed to suite us.
run dbcc loginfo(sagent_dev) display the followings:
FileId FileSize StartOffset FSeqNo Status Parity
213041664 8192 2110128
213041664 130498561960128
213041664 26091520197064
2524288039133184201064
2524288065404928204064
2524288070647808203064
25242880102105088210064
25242880107347968000
25242880112590848214264
25242880117833728213064
25242880123076608212064
Note that the virtual filesize can comprise of different configuration. In other database I can specify this logical size to whatever I like.
cheers
dave
View 2 Replies
ADVERTISEMENT
Sep 20, 2004
I have a DB that has a DB filesize of 6 gb and a transaction log filesize of 66gb!!!! This DB has a potential for alot of growth because it holds scanned documents for retention and they are in the process of scanning in several years worth of documents from what I understand.
I have my maintenance plan setup to backup the DB full overnight and transaction log backups hourly during the day. usually my transaction log backups are small - but on friday, the transction log backup was 66gb and it filled up the disk. after doing some cleanup, I ran a full and then a transaction log backup and everything seems OK. But the transaction log file itself remains at 66gb!
I'm just wondering how I can optimize my Database AND especially my maintenance plans so that the transaction log gets back down to a manageable size?? Can I or should shrink the transaction log?? Should my DB be set to 'autoshrink'???????
View 1 Replies
View Related
Jul 25, 2007
I've got a very filesize restricted database. I noticed that when I insert 1000 rows my filesize jumps to 80k, but when I delete all but 50 of those rows...the filesize actually increases to 84k. How do I make sure the filesize of my database shrinks when I delete rows?
Thanks!
View 5 Replies
View Related
Mar 8, 2008
I want to show the filesize via select query, i have in teh Database: 26208032 but on the front end it is showing this:2.620803E+07, how can i convert the bits value which is in Database table and show it as KB's.
select filesize from document_table.
Thanks.
View 6 Replies
View Related
Feb 15, 2006
Hi guys,
I have this stange problem after changing .Net CF 1.1 to 2.0.
I create the mobile database within the PPC, reading data from text files and use INSERT INTO query and build up the database. I don't recall having the 128MB filesize limit using CF 1.1 but after changing to CF 2.0 for my codes.
Dim sqlCreateTableW As Data.SqlServerCe.SqlCeCommand = ssceconn.CreateCommand()sqlCreateTableW.CommandText = "CREATE TABLE MyTbl(id integer IDENTITY(0,1) PRIMARY KEY, data ntext, ref ntext)"
sqlCreateTableW.ExecuteNonQuery()
Dim sqlIndexTableW As Data.SqlServerCe.SqlCeCommand = ssceconn.CreateCommand()
sqlIndexTableW.CommandText = "CREATE INDEX IdInd ON MyTbl (id ASC)"
sqlIndexTableW.ExecuteNonQuery()
Dim sqlInsertRowW As Data.SqlServerCe.SqlCeCommand = ssceconn.CreateCommand()
looping the code below
sqlInsertRowW.CommandText = "INSERT INTO MyTbl(data, ref) VALUES('" & str1 & "', '" & str2 & "')"
sqlInsertRowW.ExecuteNonQuery()
I have 3 columns in the table, including an index (id). I found this link http://msdn2.microsoft.com/en-us/library/ms172451.aspx and it states that:
1The default maximum size is 128 MB.
2This limit is applied only if a storage engine temporary table is used for implementing ORDER BY. If the Query Processor can leverage an existing index, there is no maximum column-number limit.
Did I do anything wrong or missed out something?
Regards.
View 5 Replies
View Related
Jan 27, 2008
Hi All,
I am placing a Matrix inside the table control for grouping requirements,but when we export the report to the Excel, the contents inside the table cell are ignored. Is there any way to get the full report exported, as per the Requirement.Please help me with this issue.
With Thanks
M.Mahendra
View 5 Replies
View Related
Oct 25, 2007
does any one have and example of how to embedd a flash swf file onto a report.??? Is it possable? any examples would be helpful.
View 1 Replies
View Related
Oct 27, 1999
I wnat to have a log file with all the updates and inserts made to the DB.
I understand I have to do a trans-log,how?
in order to make a query a transaction do I only have to declare trans...commit ??
tanx,
eyal
View 1 Replies
View Related
Jun 18, 2001
I have set up a simple trans rep from a server in the office to the web. both servers are NT4sp6aSQL 7.0. Tables only.
The publication and distribution db are on the server at the office, and there is a full time connection through the firewall.
The initialization and first replication works perfectly, but after that, there is a message from the snapshot agent that "no subscriptions needed initialization", the logreader says thare are no replicated transactions and the Distribution agent says there are no replicated transactions. What am I missing?
Thanks in advance
View 1 Replies
View Related
Dec 26, 2000
Does anyone know how to move the transaction log(s) of a LIVE database to a new location. I must move the log of a database to a new mirrored drive without any disruption to users. I cannot take the database offline or use the sp_detach_db stored procedure. Your inputs are much appreciated!
View 1 Replies
View Related
Oct 16, 2000
SQL7:
I have a 300MB db, and a transaction log near 1.3GB. Upon notification, I backed up the db log with truncate_only - no luck getting it smaller. Later, tried backup with no_log ( assuming the o/s was full - no diff)
I tried shinkfile (logfile,truncateonly) and no luck.
I tried dbcc opentrans to see if any pending trans. The db looks fine with dbcc checkdb. I managed to free up a mere 50MB. I checked the permissions on the db, and added backup db, and backup log in the db permissions for the user logged in (also tried this with sa)
I am unable to free up the space to the os. Can I somehow rid the log file and start off with a fresh log file? I need this space. As a patch I moved the log to a larger filesystem as a temporarily fix.
start/stop SQL- nothing? reboot -nothing? I played with waiting game. This log does not want to release space. The log grew from data loads.
Question1: Suggestions how to truncate this log? The contents are not really impt, but the space is.
Question2: Can I add another logfile, then use EMPTYFILE to transfer the contents to the newly added log file, then REMOVE The original logfile? In theory does this make sense?
Thanks,
Jason
View 5 Replies
View Related
Feb 1, 2001
Hello, During an intantanée arrest of replication of data base sql server 7 sp2 (nt4),
I lose keys on the subscriber, how don't have it? Thank you in advance easter
pascal
View 1 Replies
View Related
Nov 5, 2002
:confused:
Here's my situation (SQL2K)
We have a testing database we're using to convert large amounts of data from 1 system to another. We might process 5-6 million records, but don't care about being able to recover point-in-time.
I set recovery mode to simple, do a full backup every night. I keep getting large transaction logs. I manually run Shrink Database when I realize the logs are big
What can I do to prevent the logs from getting big in the first place ?? Can I prevent logging from happening ?
I keep reading various books and BOL, but I guess I don't quite "get it" yet ......
Any plain spoken, detailed suggestions would be very appreciated .... thanks in advance.
View 5 Replies
View Related
Jun 12, 2002
My database backs up just fine, but the transaction logs
are not. I created a maintenance plan for the database.
Any suggestions ?
Agent Job for transaction log backup
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0A83F1BC-EEAA-
4BDF-9374-E7FF5C1215F8 -
Rpt "d:sqldataMSSQL$PRODUCTIONLOGzeon_live_commerce DB
Maintenance Plan16.txt" -DelTxtRpt 1WEEKS -VrfyBackup -
BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 2DAYS -
CrBkSubDir -BkExt "TRN"'
The error log file for the transaction log backups is...
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'ZCHQ_SQLPRODUCTION' as 'ZCISQLSERVICE'
(trusted)
Starting maintenance plan 'zeon_live_commerce DB
Maintenance Plan1' on 6/12/2002 8:15:03 AM
Backup can not be performed on
database 'zeon_live_commerce'. This sub task is ignored.
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'zeon_live_commerce DB Maintenance
Plan1' on 6/12/2002 8:15:03 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
View 2 Replies
View Related
Nov 15, 2004
If you are set up for AutoCommit why would you or should you set a explicit transaction? I have noticed that in some called stored procudures from a "container" stored procedure. (Hope I got that right) that in the called stored procedure a Begin tran is used. Can anyone help with the why and what fors? It seems to me that you want to let SQL Server handle this becuase of the danger of leaving out a Commit or Rollback? But thats me. I may be very wrong? Thanks.
Tom
View 1 Replies
View Related
Aug 22, 2005
Hi,
I was just wondering, if I have a DB for log shipping, and change the recovery model to bulk-insert before I do a dbreindex, does the log still grow as big as the full recovery model? as in when the DB in bulk-insert model, will dbreindex still writes to the log? What i'm trying to do is try to make the log files smaller for log ship when i'm doing the db reindexing job.
Thanks.
View 1 Replies
View Related
Aug 31, 2005
I’m really scratching my head with this Transact-SQL, say you wanted to cycle through a set of rows, then perform an operation on each row, so in VB/DAO it might look like this:
Dim rsTables As Recordset, rsIndex As Recordset
Set rsTables = dbSource.OpenRecordset("SELECT * FROM INFORMATION_SCHEMA.tables")
Do While rsTables.EOF
Set rsIndex = dbSource.OpenRecordset("Select * From SysIndexes Where Name = '" & rsTables!Table_Name & "'")
DoSomethingToIndex rsIndex!Name
rsTables.MoveNext
Loop
How can you do this cycling with Trans-SQL?
View 10 Replies
View Related
Mar 28, 2001
We would like to replicate from a SQL 7 DB some data onto a SQL 2000 Server running SQL 2000. We plan to take some data off the SQL 7 DB's and create a Data Warehouse on the SQL 2000 Box.
Question: Will the data replicate successfully from the SQL 7 box onto the SQL 2000 box?
-thanks
-tom
View 1 Replies
View Related
Mar 20, 2000
Hi,
I am trying to disable transactional replication, but am having some problems. I used the wizard, however, it has taken 7 hours so far and is still not done. SQL Server (7.0) is showing the connection as runnable but it seems as if nothing is being removed. Am I missing something? Should I have done something else before running the wizard? I can't even kill the SPID.
Can anyone help or offer some advice?
Thanks in advance...
View 1 Replies
View Related
Nov 4, 2005
I have a trans log backup that runs every 15 minutes on san, works fine until I do a backup after a large load. I get the below error message. Anyone had this before?
Executed as user: DPSCSDOMsqlexec. The file on device 'n:sqllogsmdentallgdmp' is not a valid Microsoft Tape Format backup set. [SQLSTATE 42000] (Error 3242) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step fail
View 4 Replies
View Related
Aug 10, 2001
We're running Lawson software on our SQL 2000 box. We're using Veritas
Backup Exec to back up the databases to tape. I'm also using a Maint
Plan for an extra backup (kinda redundant, but I need the practice at
all this). I just added transaction logs to the maint plan (so I
thought) but I only see a log for 1 of the 3 databases.
Also, my trans log file is 11 gig. I thought backing up the trans logs
would get the trans to delete afterwards (databases are recovery
model=full). Can anyone point a newbie in the right direction.
Fortunately it's not in production yet, and we haven't had any disasters
(yet).
View 1 Replies
View Related
Aug 10, 2001
No I did not write this below, this is from a vendor, I used profiler and I believe their SP is causing a blocking problem on their vendor supplied DB. It thought at the least always have a begin end or a begin trans commit trans. ANy quick opinions greatly appreciated
create procedure write_planned_service_rec
@p1 varchar(20),@p2 varchar(20),@p3 varchar(20),@p4 varchar(20),@p5 varchar(20),
@p6 varchar(20),@p7 varchar(20),@p8 varchar(20),@p9 varchar(20),
@p10 varchar(20),@p11 varchar(20),@p12 varchar(20),@p13 varchar(20),@p14
varchar(20),
@p15 varchar(20),@p16 varchar(20),@p17 varchar(20),@p18 varchar(20),@p19
varchar(20),
@p20 varchar(20)
AS
IF @p20 = 'P'
update patient set date_insurance_updated = getdate() where patient_id =
@p1 and practice_id = @p13
View 1 Replies
View Related
Jan 20, 2005
Is there anyway in SQL to backup the Database and Transaction log without truncte the Transaction log? :o. I would like to keep all or as much of the Transaction log info as possable. I know the Trans log will get large but HD space is not a problem. I use the transaction log to report field changes usung Log Explorer.
Thanks
View 10 Replies
View Related
Feb 20, 2007
Guys,
Should the transaction log of the system databases be backed up and if so, how often should this be done, generally?
Currently, we are backing up the system databases daily. The transaction log of the system databases, however, is not backed up.
Recently, I was wondering what exactly could happen, which would imply that we need to back up the transaction log of the system databases.
As far as I understand, 'msdb' contains things like jobs, dts. As such, if no jobs/dts are added in the middle of the day, there is no need to back up the trans log of this db - there could even be no reason, in such case, to back this db up at all!
'master' db would change if we add/modify objects/properties and so on ... so unless many changes are made, no need for backing up its trans log.
Having said that, could anyone enlighten me as to whether it is necessary to back up the transaction log of the system dbs and/or what it depends on and/or when it is suggested to do so?
Thanks you
View 2 Replies
View Related
Aug 12, 2014
Writing SQL scripts with insert trans. If one of the inserts failed rollback all previous inserts. Here what I come up with but I don't think it will work.
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
Insert 1
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
[Code] ....
View 1 Replies
View Related
Mar 12, 2008
May I know any reason why my Transaction Log Backup job keeps failing while the particular database set to "Standby /Read-Only"?
Kindly help. Thank You.
Regards,
Jimmy Liew
View 7 Replies
View Related
May 3, 2007
I have a 2000 DB which is log-shipping to another server. The log has grown huge and needs to be made smaller. Can I use DBCC SHRINKFILE without damaging the log-shipping? I know that truncating the log destroys log-shipping, but shrinking should logically not cause any harm. Do you know? Is there a better way to reduce the size of the log file without damaging log-shipping??
Thanks!
View 7 Replies
View Related
Feb 5, 2007
Hi!
We have a vendor's case-sensitive db which we have to Trans Rep our case-insensitive data into. Can't do it directly as it violates the Rep rule against different collations. Is there some trick or work-around we can use to implement this?
Thanks,
Michael
View 1 Replies
View Related
May 2, 2006
I am sure this has been asked multiple times before, but I have a DB (SQL 2K) that is involved in transactional replication. The log periodically grows to a seriously big size. How can I shrink it without damaging replication? I have opened SSIS (SQL 2005 is the subscriber) and tried to shrink the file, but to no avail. It reckons I could shrink it to 0MB, but I feel this would throw out replication.
Any help appreciated.
View 3 Replies
View Related
Mar 20, 2007
UPDATE #2: When it said "Do you want to install Microsoft SQL Server" I said "yes" and that caused it to work. I exited and re-ran and now the print runs w/o the "install SQL Server" (If the prompt had said "Do you want to install the print dialog" we wouldn't be having this discussion...)
UPDATE: After posting this i discovered that the same thing occurs when attempting to print the report direct from IE6: First a dialog pops up "Do you want to install this software?" Name: Microsoft SQL Server. When I click "Don't Install" I get the dialog "unable to load client print control." Since this happens direct from IE6 I suspect it's browser settings. I'll resume tomorrow and post a followup.
My WinForm C# app integrates Reporting Services by calling them from WebBrowser controls. The problem is attempts to print cause a dialog: "unable to load client print control."
I've read prior posts that say "enable Active-X in your browser" - I don't know how to do that from a WebBrowser control.
Any ideas how to support Reporting Services "Print" from within a WebBrowser control?
RELATED THREADS
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=332145&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=264478&SiteID=1
View 1 Replies
View Related
Aug 28, 2001
Hi Every one,
According to our stratergy we are taking trans log backup of every user database everuy two hours.things seem to work great and with success at the day time and at night once at 12.03 and 2.03 they fail and when i checked the error log i found that there is only one database for which its failing and it gives the following error.
Database ors: Transaction Log Backup...
Destination: [D:BackupTranLogorsors_tlog_200108280203.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot allow BACKUP LOG because file 'ors_Data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
We had a option called select into/bulccopy set which i disabled once this failure has occured but still the problem persists....there are no other options set...i am out of resources i donno what to do..could you suggest me something and how to solve this problem its happening in prod.
Thanks for your help.
Kavitha
View 1 Replies
View Related
Aug 29, 2001
Hi Every one,
According to our stratergy we are taking trans log backup of every user database everuy two hours.things seem to work great and with success at the day time and at night once at 12.03 and 2.03 they fail and when i checked the error log i found that there is only one database for which its failing and it gives the following error.
Database ors: Transaction Log Backup...
Destination: [D:BackupTranLogorsors_tlog_200108280203.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot allow BACKUP LOG because file 'ors_Data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
We had a option called select into/bulccopy set which i disabled once this failure has occured but still the problem persists....there are no other options set...i am out of resources i donno what to do..could you suggest me something and how to solve this problem its happening in prod.
As Said by someone i took a full backup...tooooo it does not solve the issue.
Thanks for your help.
Kavitha
View 6 Replies
View Related
Dec 6, 1999
This is a silly question but I am not sure of the answer.
Each day I do a complete DB backup and houly I backup the Translog file with the append mode.
Question Is the Transaction log truncated each time I do a Transaction Log Backup?
View 3 Replies
View Related
